为多个操作创建一个触发器

问题描述：

我的问题是，我可以为一个表的多个操作（插入/更新/删除）创建一个触发器吗？像这样的东西：

My question is, can I create one trigger for multiple operations (insert/update/delete) on one table ? Something like this :

Create trigger [dbo].[TR_AUDIT_TESTAUDIT]
   ON [dbo].[testaudit]
    AFTER UPDATE, INSERT, DELETE 
    AS BEGIN

    -- prepare the audit data 

    case the operation is insert then 
    case the operation is delete then 
    case the operation is update then 

    -- process auditdata 

   END

现在我必须为该任务创建3个触发器，而我可以将它们组合为一个！

Now I have to create 3 triggers for this task while I can combine them into one!

答

没关系，我明白了：

Create trigger [dbo].[TR_AUDIT_TESTAUDIT]
    ON [dbo].[testaudit]
    AFTER INSERT, UPDATE, DELETE 
    AS 
BEGIN
    SET NOCOUNT ON;
    declare @action nvarchar(1) 

    set @action = 'I' -- always I 

    if exists(select top 1 1 from deleted) and not exists(select top 1 1 from inserted)         
    set @action = 'D' 

    if exists(select top 1 1 from deleted) and  exists(select top 1 1 from inserted)        
    set @action = 'U'        
END

为多个操作创建一个触发器

相关推荐