sql-server - 如何在 SQL Server 中使用存储过程创建动态触发器

Question

我正在使用 SQL Server 触发器进行审计跟踪，以识别表上的插入、更新和删除。下面是我的表格和触发器：

CREATE TABLE [dbo].[AuditTrail]
(
   [AuditId] [INT] IDENTITY(1,1) NOT NULL,
   [DateTime] [DATETIME] NOT NULL,
   [TableName] [NVARCHAR](255) NOT NULL,
   [AuditEntry] [XML] NULL,

   CONSTRAINT [PK_AuditTrail] PRIMARY KEY CLUSTERED 
)

CREATE TABLE [dbo].[Employee]
(
   [ID] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid()),
   [NameEmployee] [NVARCHAR](255) NOT NULL,

   CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
)

CREATE TABLE [dbo].[Transaction]
(
   [ID] [UNIQUEIDENTIFIER] NOT NULL DEFAULT (newid()),
   [NameTransaction] [NVARCHAR](255) NOT NULL,

   CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
)

CREATE TRIGGER AuditEmployee 
ON [dbo].[Employee]
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF (SELECT COUNT(*) FROM deleted) > 0 
    BEGIN
        DECLARE @AuditMessage XML
        SET @AuditMessage = (SELECT * FROM deleted FOR XML AUTO) 

        INSERT INTO AuditTrail (DateTime, TableName, AuditEntry)  
        VALUES (GETDATE(), 'Simple', @AuditMessage)
    END
END
GO

我为员工表创建了一个触发器，但那是一个静态触发器。存储过程如何动态触发取决于我们拥有的表的数量，除了AuditTrail表？

标签： sql-serverstored-proceduresdatabase-trigger