SQLSERVER触发器内INSERT,UPDATE,DELETE三种状态

发布时间：2020-12-12 14:45:29 所属栏目：MsSql教程来源：网络整理

导读：一个触发器内三种INSERT,UPDATE,DELETE状态 CREATE ? TRIGGER ? tr_T_A ? ON ? ? T_A ? for ? INSERT,DELETE ? ? ? ?? ??如IF ? exists ? (select ? * ? from ? inserted) ? and ? not ? exists ? (select ? * ? from ? deleted) ? 则为 ? INSERT ?? ??如IF

一个触发器内三种INSERT,UPDATE,DELETE状态

CREATE ? TRIGGER ? tr_T_A ? ON ? ? T_A ? for ? INSERT,DELETE ? ? ? ?? ??如IF ? exists ? (select ? * ? from ? inserted) ? and ? not ? exists ? (select ? * ? from ? deleted) ? 则为 ? INSERT ?? ??如IF ? exists(select ? * ? from ? inserted ? ) ? and ? exists ? (select ? * ? from ? deleted) ? 则为 ? UPDATE ?? ??如IF ? exists ? (select ? * ? from ? deleted) ? and ? not ? exists ? (select ? * ? from ? inserted)则为 ? DELETE ?
插入操作（Insert）：Inserted表有数据，Deleted表无数据? 删除操作（Delete）：Inserted表无数据，Deleted表有数据? 更新操作（Update）：Inserted表有数据（新数据），Deleted表有数据（旧数据）? ? 笔者用到的案例: create TRIGGER [risk].[Entry_Head_port_Exchange_Trigger] ?? ON ?[RiskH800].[risk].[ENTRY_HEAD] ?? AFTER INSERT,UPDATE AS? DECLARE @COUNT INT ?? ?DECLARE @MANUAL_NO_COUNT INT ?? ?IF EXISTS (SELECT 1 FROM INSERTED) ?? ? ? IF EXISTS(SELECT 1 FROM DELETED) ?? ? ? BEGIN ?? ? ? SELECT @COUNT = COUNT(*) FROM risk.ENTRY_HEAD_port_EXCHANGE WHERE ENTRY_ID = (select ENTRY_ID FROM DELETED) ?? ? ? SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) FROM DELETED where substring(MANUAL_NO,1,1)= 'B' or substring(MANUAL_NO,1)='C' ?? ? ? ? ? IF @COUNT <=0 AND @MANUAL_NO_COUNT>0 ?? ? ? ? ? INSERT INTO risk.ENTRY_HEAD_port_EXCHANGE(ENTRY_ID,CREATE_DATE)(SELECT ENTRY_ID,getdate() FROM DELETED) ?? ? ? END ?? ? ? ELSE ?? ? ? BEGIN ?? ? ? SELECT @COUNT = COUNT(*) FROM risk.ENTRY_HEAD_port_EXCHANGE WHERE ENTRY_ID = (select ENTRY_ID FROM INSERTED) ?? ? ? ? ? SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) FROM INSERTED where substring(MANUAL_NO,getdate() FROM INSERTED) ?? ? ? END

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!