To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.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.
|
