Need More Information?
The previous trigger examples were very simple and adequate for many audit tasks, but you can do more. Suppose you want to know which column is updated. In this case, you'd use a statement similar to the one in Listing 3, which passes the text "Column UnitPrice modified" as a parameter to the INSERT INTO values clause. The IF UPDATE clause can be included as many times as required to include all the information you want to document.
Listing 3: Identifying the Column
CREATE TRIGGER AuditUpdate ON dbo.Products
DECLARE @comments VarChar(255)
SELECT @comments='Column UnitPrice modified'
INSERT INTO AuditTrail(TableName, ActionTaken,
ActionUser, ActionDate, Comment) VALUES
First add a column to the AuditTrail table to store this information. Let's assume that the new column is called Comments, and it's VarChar(1024) so that there's plenty of room. To track changes to a particular column, you could use the IF UPDATE clause as illustrated in Listing 3, which passes the text "Column UnitPrice modified" as a parameter to the INSERT INTO values clause. The IF UPDATE clause could be included as many times as required to pass all the information you want to document.
Instead of canceling your canoe trip, set out a few trigger traps and let SQL Server snag your unauthorized user. On Monday, after your relaxing weekend, create a report based on the AuditTrail table. More than likely, the information your boss needs will be in that report and you'll become the boss's new favorite employee.