用于日志记录的触发器内的存储过程

Question

我对多个表使用相同的触发器来记录DML事件，因此我有很多冗余代码。如何编写存储过程并在这些触发器中调用它来记录数据？

这是我的触发器看起来像

CREATE OR ALTER TRIGGER [Person].tr_logInsertDeleteOrUpdateemployee
ON [Person].employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @tableName varchar(100) = '[Person].employee'
IF EXISTS (SELECT TOP 1 * FROM inserted) 
AND EXISTS (SELECT TOP 1 * FROM Deleted)
BEGIN
INSERT INTO dbo.DMLLogs
SELECT updatedRecord = 'updated row', @tableName, ID, SYSTEM_USER, GETDATE()  
FROM deleted
END
IF EXISTS (SELECT TOP 1 * FROM inserted) 
AND NOT EXISTS (SELECT TOP 1 * FROM Deleted)
BEGIN
INSERT INTO dbo.DMLLogs
SELECT insertedRecord = 'inserted', @tableName, ID, SYSTEM_USER, GETDATE() 
FROM inserted
END
IF EXISTS (SELECT TOP 1 * FROM deleted) 
AND NOT EXISTS (SELECT TOP 1 * FROM inserted)
BEGIN
INSERT INTO dbo.DMLLogs
SELECT deletedRecord = 'deleted from', @tableName, ID, SYSTEM_USER, GETDATE() 
FROM deleted
END
END

Answer 1

抛开是否有比创建触发器更好的选项这个问题不谈，插入和删除的虚拟表在存储过程中是不可见的，所以实际上没有办法做到这一点。这里的通常做法是自动创建样板触发器，这样它们都是从一个模板创建的。类似于：

create schema admin
go
create or alter proc admin.GenerateAuditingTriggers
as
begin
declare c cursor local for
select name, schema_name(schema_id)
from sys.tables 
where schema_id in (schema_id('dbo'))

open c
declare @tableName sysname
declare @schemaName sysname
fetch next from c into @tableName, @schemaName 
while @@FETCH_STATUS = 0
begin
declare @sql nvarchar(max) = concat(
'
CREATE OR ALTER TRIGGER  ',quotename(@schemaName),'.',quotename('tr_logInsert' + @tableName),'
ON ',quotename(@schemaName),'.',quotename(@tableName),'
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @tableName varchar(100) = ''',quotename(@schemaName),'.',quotename(@tableName),'''
INSERT INTO dbo.DMLLogs
SELECT updatedRecord = ''inserted row'', @tableName, ID, SYSTEM_USER, GETDATE()  
FROM inserted
END
')
print @sql
exec (@sql)
print 'trigger created'
print ''
fetch next from c into @tableName, @schemaName 
end
close c 
deallocate c
end