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

来源：互联网发布：java信息系统管理开发编辑：程序博客网时间：2024/04/28 14:40

转自：http://yunlongzheng.blog.51cto.com/788996/780110

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

CREATE TRIGGER tr_T_A ON T_A for INSERT,UPDATE,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,UPDATEAS 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,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,1,1)= 'B' or substring(MANUAL_NO,1,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 INSERTED)       END

0 0