Table history trigger in SQL Server?
I'd like to create a trigger that writes to a history table with inserted values and before and after update values. I would also like to include as much information about the account doing the update as is possible. How would i include the account information in my trigger?
Here is what I have so far:
CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', '???'
FROM deleted
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber开发者_如何学Python, GETDATE(), 'AFTER UPDATE', '???'
FROM inserted
What do i put in place of the '???'?
If each user has an account, you can use the SYSTEM_USER
function to determine the current user. However, if all your connections go through a proxy account, as is typical in most web site setups, then you have to rely on the proper userId being passed to the Update statement:
CREATE TRIGGER [update_history] ON MyTable
FOR UPDATE
AS
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'BEFORE UPDATE', inserted.userId
FROM MyTable
Join inserted
On inserted.id = MyTable.id
INSERT MyTable_History (id, BudgetNumber, PositionNumber, ModifiedDate, action, userId)
SELECT id, BudgetNumber, PositionNumber, GETDATE(), 'AFTER UPDATE', userId
FROM inserted
FWIW, if you are using SQL Server 2008, there is a much better alternative than writing triggers to track data changes. Take a look at Change Data Capture.
use ORIGINAL_LOGIN()
you can see further definition of what it does here SQL Server - current user name
精彩评论