Q
Problem solve Get help with specific problems with your technologies, process and projects.

Code trigger that captures login and system date

Expert Andrew Novick develops a 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.
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 last published in August 2005

Dig Deeper on SQL Server Security

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close