MSSQL记录表字段数据变化的相关SQl
在软件实施过程中,也许会有这样的问题: 表中数据出现非预期的结果,此时不确定是程序问题,哪个程序,存储过程,触发器.. 或还是人为修改的结果,此时可以用触发器对特定的表字段做跟踪监视,记录每次新增,修改,删除此字段值的操作详细信息(含登录名,主机名,IP地址,执行的TSQL语句,程序名等等), 以利于问题的排查.
-- 建测试表
CREATE TABLE sto
(
id INT NOT NULL, -- 主键字段
de DATETIME -- 被跟踪的字段
CONSTRAINT pk_sto
PRIMARY KEY (id)
);
-- 建日志表
CREATE TABLE log_sto
(
logid INT NOT NULL IDENTITY(1, 1), -- 日志序号(日志主键)
operate VARCHAR(10), -- 操作类型 如Insert,Update,Delete.
id INT, -- 原表ID(主键)
old_de DATETIME, -- de字段旧值
new_de DATETIME, -- de字段新值
spid INT NOT NULL, -- spid
login_name VARCHAR(100), -- 登录名
prog_name VARCHAR(100), -- 程序名
hostname VARCHAR(100), -- 主机名
ipaddress VARCHAR(100), -- IP地址
runsql VARCHAR(4000), -- 执行的TSQL代码
UDate DATETIME -- 操作日期时间
CONSTRAINT pk_logsto
PRIMARY KEY (logid)
);
GO
-- 建跟踪触发器
CREATE TRIGGER tr_sto
ON sto
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @di TABLE
(
et VARCHAR(200),
pt VARCHAR(200),
ei VARCHAR(MAX)
);
INSERT INTO @di
EXEC ('dbcc inputbuffer(@@spid)');
DECLARE @op VARCHAR(10);
SELECT
@op = CASE WHEN EXISTS ( SELECT 1 FROM inserted ) AND EXISTS (SELECT 1 FROM deleted)
THEN 'Update'
WHEN EXISTS ( SELECT 1 FROM inserted ) AND NOT EXISTS (SELECT 1 FROM deleted)
THEN 'Insert'
WHEN NOT EXISTS(SELECT 1 FROM inserted ) AND EXISTS (SELECT 1 FROM deleted)
THEN 'Delete'
END;
IF @op IN (
'Update', 'Insert'
)
BEGIN
INSERT INTO log_sto
(
operate,
id,
old_de,
new_de,
spid,
login_name,
prog_name,
hostname,
ipaddress,
runsql,
UDate
)
SELECT
@op,
n.id,
o.de,
n.de,
@@spid,
(
SELECT
login_name
FROM
sys.dm_exec_sessions
WHERE
session_id = @@spid
),
(
SELECT
program_name
FROM
sys.dm_exec_sessions
WHERE
session_id = @@spid
),
(
SELECT
hostname
FROM
sys.sysprocesses
WHERE
spid = @@spid
),
(
SELECT
client_net_address
FROM
sys.dm_exec_connections
WHERE
session_id = @@spid
),
(
SELECT TOP 1
ISNULL(ei, '')
FROM
@di
),
GETDATE()
FROM
inserted n
LEFT JOIN
deleted o
ON o.id = n.id;
END;
ELSE
BEGIN
INSERT INTO log_sto
(
operate,
id,
old_de,
new_de,
spid,
login_name,
prog_name,
hostname,
ipaddress,
runsql,
UDate
)
SELECT
@op,
o.id,
o.de,
NULL,
@@spid,
(
SELECT
login_name
FROM
sys.dm_exec_sessions
WHERE
session_id = @@spid
),
(
SELECT
program_name
FROM
sys.dm_exec_sessions
WHERE
session_id = @@spid
),
(
SELECT
hostname
FROM
sys.sysprocesses
WHERE
spid = @@spid
),
(
SELECT
client_net_address
FROM
sys.dm_exec_connections
WHERE
session_id = @@spid
),
(
SELECT TOP 1
ISNULL(ei, '')
FROM
@di
),
GETDATE()
FROM
deleted o;
END;
END;
GO
--> 测试DML操作
-- 操作1
INSERT INTO sto
(
id,
de
)
VALUES
(
1, '2012-01-01 05:06:07'
);
GO
-- 操作2
INSERT INTO sto
(
id,
de
)
VALUES
(
2, '2012-01-01 06:06:07'
);
GO
-- 操作3
UPDATE
sto
SET
de = getdate()
WHERE
id = 2;
GO
-- 操作4
UPDATE
sto
SET
de = getdate()
WHERE
id = 1;
GO
-- 操作5
INSERT INTO sto
(
id,
de
)
VALUES
(
5, '2012-01-01 15:26:37'
);
GO
-- 操作6
DELETE sto
WHERE
id = 2;
GO