CREATE TABLE DataTable ([Id] int identity (1,1) primary key , UnMonitoriedColumn char(10) , MonitoredColumn varchar(10)...
CREATE TABLE myAuditTable (AuditEventID int identity (1,1) primary key
, USERLOGIN nvarchar(128)
, ChangeDT datetime
Note DataTable as one column that is going to be monitored and another that is not monitored. myAuditTable has its own identity column, AuditEventID, so that events the occur at the same time can be distinguished. It might also be valuable to add additional columns to the table to record the ID column from the modified row and even the old and new values of MonitoredColumn. I'll leave that for another article.
Next comes the trigger. Here's the script to create the trigger.
CREATE TRIGGER DataTable_Audit on DataTable
FOR INSERT, UPDATE
AS IF UPDATE(MonitoredColumn)
INSERT INTO myAuditTable (USERLOGIN, ChangeDT)
VALUES (SYSTEM_USER, getdate())
The body of the trigger contains only one statement. That statement uses the UPDATE function to test if MonitoredColumn has been changed, and if so it records the system user and date/time of the change. The UPDATE function is only available inside triggers for the purpose of detecting changes in individual columns. Beware that if more than one row is changed by a SQL statement, the UPDATE(MonitoredColumn) clause will be true if MonitoredColumn is changed in any of the rows modified by the statement.
The SYSTEM_USER function identifies the SQL Login change. This might be either a SQL Server login or the Windows login if that's how the user connected to SQL Server. In environments, such as Web applications where it's common to use only one SQL Server login for all users of the application, SYSTEM_USER will be the same for all users and not very helpful. In these situations, the Web application would have to supply additional information about the user in order to identify him.
Next, let's test the trigger with this script:
DECLARE @ID int
INSERT INTO DataTable (UnMonitoriedColumn, MonitoredColumn)
VALUES ('ABC', 'DEF')
SELECT @ID = SCOPE_IDENTITY()
SET MonitoredColumn = 'GHI'
WHERE [Id] = @ID
SET UnMonitoriedColumn = 'JKL'
WHERE [Id] = @ID
SELECT * FROM myAuditTable
One row is inserted into DataTable and the ID column captured in the @Id variable. Next two updates are performed, one that changes MonitoredColumn and the second that does not. Here are the results of the query:
AuditEventID USERLOGIN ChangeDT
------------ -------------------- ------------------------------------------------------
1 NSandrew 2005-07-18 03:13:20.403
2 NSandrew 2005-07-18 03:13:20.403
(2 row(s) affected)
There are two rows in myAuditTable because the trigger audits both INSERTs and UPDATEs. The first row is for the INSERT and the second for the first UPDATE statement. The second UPDATE statement didn't change MonitoredColumn, so there are no rows added to myAuditTable.
Do you have comments on this Ask the Expert Q&A? Let us know.
Dig Deeper on SQL Server Security
Related Q&A from Andrew Novick
Can't decide whether to use nullable fields in a table. Development expert Andrew Novick discusses what to do. Continue Reading
If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started. Continue Reading
Development expert Andrew Novick gives you the resources to improve your stored procedure writing skills. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.