SQL SERVER触发器中判断是触发的新增,修改,还是删除,及使用方法

多表间的新增,修改,删除触发器事件

判断方法 

create trigger tgr_ba0
on ba2
after update, delete,insert--这里也能判断是修改,删除,还是新增
as
    --修改
    if exists(select 1 from inserted) and exists(select 1 from deleted)
    begin
        insert into log(action) values('updated');--修改
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log(action) values('inserted');--新增
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log(action) values('deleted');--删除
    end
go

使用方法

USE [SQLHIS]
GO
/****** Object:  Trigger [dbo].[add_lis_sqh]    Script Date: 10/01/2020 08:43:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[add_lis_sqh]--CREATE新增  ALTER修改
   ON  [dbo].[L_LIS_SQD] 
   AFTER    INSERT,DELETE,UPDATE
AS 
BEGIN
---新增加
    if(exists(select 1 from inserted) and not exists(select 1 from deleted))
    begin  
        
        INSERT  INTO SQLHIS.dbo.lis_sqh (
        [sqh]
      ,[sqsj]
      ) select 
      [DOCTREQUESTNO]
      ,[REQUESTTIME]
        FROM Inserted AS A
        ---
    	SET NOCOUNT ON;
    end 
 -- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
        
    --end
    -- Insert statements for trigger here
 
END

 

posted @ 2020-10-01 09:05  离。  阅读(231)  评论(0编辑  收藏