sql - 如何将一个触发器写入所有表到 SQL Server 2008

Question

我写了一个触发器，它在更新 Employee 表时触发。

当触发器触发时，它会将新记录插入到 Transact 表中。

ALTER TRIGGER [PROJECTS].[after_updateEmployee] ON [PROJECTS].[Employee]
AFTER  UPDATE
AS DECLARE @tablen varchar(30) , @modyfire  varchar(15),@modyfied_date datetime,@columname   varchar(20),@action varchar(6)
SET @tablen ='Employee'
set @modyfire =(SELECT SYSTEM_USER)
set @modyfied_date =(select CURRENT_TIMESTAMP)
SET @columname = (SELECT TOP 1 E_id  FROM PROJECTS.Employee ORDER BY RV DESC)
set @action ='UPDATE'
BEGIN
INSERT INTO PROJECTS.Transact values(@tablen,@modyfire,@modyfied_date,@columname,@action)
END

我的数据库中有更多表。那么有什么方法可以对我数据库中的所有表使用此触发器。

谢谢。

Answer 1

基于 this回答你可以尝试以下方法:

将新类型创建为包含对象 ID 和表的表:

create type table_rows as table
(
    row_id      uniqueidentifier not null,  -- deleted object id
    table_name  varchar(50) not null        -- object's table
);

此函数在 auto_log 表中记录每个删除的对象:

create procedure mark_as_deleted(@r as table_rows readonly) as
begin
    insert into auto_log (id, row_id, table_name, t_deleted, editor)
        select NEWID(), row_id, table_name, getdate(), current_user from @r
end

最后是必须应用于每个表的触发器:

create trigger on_delete_object on [dbo].[object]
instead of delete as
begin
    declare @table table_rows

    insert into @table
        select id, object_schema_name(parent_id) + '.' + object_name(parent_id)
        from deleted
        join sys.triggers on object_id = @@PROCID

    -- Do what ever you need with deleted data
    exec mark_as_deleted @table
end

这种方法的优点是所有触发器都很熟悉，用于处理已删除数据的代码放在单独的过程中