只要表有增删改操作,都会记录所有表变更的操作记录
-- 创建审计表
CREATE TABLE TableChangeAudit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
TableName NVARCHAR(128),
OperationType CHAR(1), -- 'I'=Insert, 'U'=Update, 'D'=Delete
PrimaryKeyValue NVARCHAR(255),
OldData NVARCHAR(MAX),
NewData NVARCHAR(MAX),
ChangeTime DATETIME DEFAULT GETDATE(),
ChangedBy NVARCHAR(128)
);
-- 为特定表创建触发器
CREATE TRIGGER tr_YourTable_Audit
ON YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- 处理插入操作
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO TableChangeAudit (TableName, OperationType, PrimaryKeyValue, NewData, ChangedBy)
SELECT
'YourTable',
'I',
CAST(i.ID AS NVARCHAR(255)), -- 假设ID是主键
(SELECT * FROM inserted FOR JSON AUTO),
SUSER_SNAME()
FROM inserted i;
END
-- 处理更新操作
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO TableChangeAudit (TableName, OperationType, PrimaryKeyValue, OldData, NewData, ChangedBy)
SELECT
'YourTable',
'U',
CAST(i.ID AS NVARCHAR(255)),
(SELECT * FROM deleted WHERE ID = i.ID FOR JSON AUTO),
(SELECT * FROM inserted WHERE ID = i.ID FOR JSON AUTO),
SUSER_SNAME()
FROM inserted i;
END
-- 处理删除操作
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO TableChangeAudit (TableName, OperationType, PrimaryKeyValue, OldData, ChangedBy)
SELECT
'YourTable',
'D',
CAST(d.ID AS NVARCHAR(255)),
(SELECT * FROM deleted FOR JSON AUTO),
SUSER_SNAME()
FROM deleted d;
END
END
- 本文标题: 数据库异动记录
- 文章分类:【SQLServer】
- 非特殊说明,本文版权归【胡同里的砖头】个人博客 所有,转载请注明出处.