触发器学习

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [tri_t_advisory] 
   ON   [dbo].[T_ADVISORY]
   AFTER insert,update,delete
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	---新增判断，只有inserted有记录
   IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted) begin
      INSERT INTO T_MiddleExchange
                (businessId,businessTableName,eventType,WZFINISHED,JCFINISHED)
      select adviid,'t_advisory','新增','Y','N' from inserted;
  --更新判断，inserted，deleted均有记录
   end else IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)begin
    INSERT INTO T_MiddleExchange
                (businessId,businessTableName,eventType,WZFINISHED,JCFINISHED)
      select adviid,'t_advisory','更新','Y','N' from inserted;
  --删除判断,只有deleted有记录 
 end else IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)begin
     INSERT INTO T_MiddleExchange
                (businessId,businessTableName,eventType,WZFINISHED,JCFINISHED)
     select adviid,'t_advisory','删除','Y','N' from Deleted;
   end



END