在軟體實施過程中,也許會有這樣的問題: 表中數據出現非預期的結果,此時不確定是程式問題,哪個程式,存儲過程,觸發器.. 或還是人為修改的結果,此時可以用觸發器對特定的表欄位做跟蹤監視,記錄每次新增,修改,刪除此欄位值的操作詳細信息(含登錄名,主機名,IP地址,執行的TSQL語句,程式名等等), 以利 ...
在軟體實施過程中,也許會有這樣的問題: 表中數據出現非預期的結果,此時不確定是程式問題,哪個程式,存儲過程,觸發器.. 或還是人為修改的結果,此時可以用觸發器對特定的表欄位做跟蹤監視,記錄每次新增,修改,刪除此欄位值的操作詳細信息(含登錄名,主機名,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
