Code trigger that captures login and system date

Code trigger that captures login and system date

I need code for a trigger that captures the user/login and system date after a specific column has been updated in a table.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

This request is pretty easy to fulfill. Let's start with two tables: A table for data named DataTable and one to record the SQL Server Login that performed the change and the date and time of the change. The latter table is named myAuditTable. Here's the script to create the two tables.

CREATE TABLE DataTable ([Id] int identity (1,1) primary key
, UnMonitoriedColumn char(10)
, MonitoredColumn varchar(10)
) GO

CREATE TABLE myAuditTable (AuditEventID int identity (1,1) primary key
, USERLOGIN nvarchar(128)
, ChangeDT datetime
)
GO

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())
GO

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()
UPDATE DataTable
SET MonitoredColumn = 'GHI'
WHERE [Id] = @ID
UPDATE DataTable
SET UnMonitoriedColumn = 'JKL'
WHERE [Id] = @ID
SELECT * FROM myAuditTable
GO

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.

This was first published in August 2005

Back to top