首页 SQLServer 数据库异动记录

数据库异动记录

作者:胡同里的砖头 围观群众:38 更新于:2025-08-01

只要表有增删改操作,都会记录所有表变更的操作记录

-- 创建审计表
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】
  • 非特殊说明,本文版权归【胡同里的砖头】个人博客 所有,转载请注明出处.
留言评论
站点声明:
1、本站【胡同里的砖头】个人博客,借鉴网上一些博客模板,取其各优点模块自行拼装开发,本博客开发纯属个人爱好。
2、所有笔记提供给广大用户交流使用,可转载,可复制,纯个人开发所遇问题锦集记录使用
Copyright © huzlblog.com All Rights Reserved. 备案号:苏ICP备2021056683号-8