关闭

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

3305人阅读 评论(0) 收藏 举报
分类:

一个触发器内三种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,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,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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:707652次
    • 积分:8669
    • 等级:
    • 排名:第2211名
    • 原创:105篇
    • 转载:485篇
    • 译文:4篇
    • 评论:40条
    最新评论
    我的百度空间