简述SQL SERVER触发器内INSERT,UPDATE,DELETE的三种状态 - MSSQL教程

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

（编辑：佛山站长网）

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