在更新后创建一个触发器，以循环遍历每个更新的列

Question

我想跟踪特定表的所有更改。所以我想在表更新时触发一个触发器，对于每个更新的字段，我想在审计日志表中插入一行，列为旧值和新值。

例如，假设我有一个类似的员工表

CREATE TABLE [dbo].[Employee]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Current] [nvarchar](50) NULL,
[Password] [nvarchar](50) NULL,
[Active] [bit] NOT NULL
)

像这样的日志表

CREATE TABLE [dbo].[Logs]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[Puser] [nchar](10) NULL,
[WinUser] [nchar](20) NULL,
[Computer] [nchar](20) NULL,
[Type] [nchar](20) NULL,
[ObjectType] [nchar](20) NULL,
[ObjectName] [nchar](20) NULL,
[Action] [nchar](20) NULL,
[RecordID] [nchar](20) NULL,
[FIeldName] [nchar](20) NULL,
[OldValue] [nvarchar](50) NULL,
[NewValue] [nvarchar](50) NULL,
[ParentName] [varchar](20) NULL,
[ParentID] [varchar](20) NULL,
[RowDump] [nvarchar](max) NULL
)

当我更改员工的密码和活动列时，我想在Logs表中输入2行(包括recordid中的员工id、fieldname中的列名、oldvalue、newvalue(

提前感谢

Answer 1

实现这类操作的最佳方法是使用SQL Server中的现有工具，如变更数据捕获和SQL审核。

---

但是，如果真的想要滚动自己的日志记录，以下内容应该会让您开始。

不清楚您的一些日志记录列应该代表什么，所以我省略了它们。这似乎也有些过头了。

CREATE TRIGGER dbo.LogChanges ON dbo.Employee
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
RETURN;    -- bail early if no rows

INSERT dbo.Logs (
TimeStamp,
WinUser,
Computer,
Action,
OldValue,
NewValue,
ParentID
)
SELECT
GETUTCDATE(),
SUSER_NAME(),
HOST_NAME(),
IIF(i.ID IS NOT NULL, IIF(d.ID IS NOT NULL, 'U', 'I'), 'D'),
v.oldVal,
v.newVal,
ISNULL(i.ID, d.ID)
FROM inserted i
FULL JOIN deleted d ON d.ID = i.ID    -- full join on primary key
CROSS APPLY (VALUES
(i.FirstName, d.FirstName),    -- unpivot all columns
(i.LastName, d.LastName),
(i.[Current], d.[Current]),
(i.Password, d.Password)
) v(newVal, oldVal)
WHERE EXISTS (SELECT v.newVal EXCEPT SELECT v.oldVal);    -- null-aware comparison

如果列类型是混合的，则应至少将CROSS APPLY (VALUES中的第一个值强制转换为sql_variant。

db<gt；小提琴

我注意到您的一些数据类型是有问题的，例如可以为null的列、长度以及对象和用户名列应该是sysname。纯文本密码也是个坏主意。