Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Use SQL Server Triggers to Track Unauthorized Database Changes  : Page 4

SQL Server can do all the work of tracking database modifications by responding to changes with predefined tasks.


advertisement
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 FOR UPDATE AS DECLARE @comments VarChar(255) IF UPDATE(UnitPrice) SELECT @comments='Column UnitPrice modified' INSERT INTO AuditTrail(TableName, ActionTaken, ActionUser, ActionDate, Comment) VALUES ('Products','U',User_Name(),GetDate(), @comments)



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.

Trigger Happy
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.



Susan Sales Harkins (harkins@iglou.com) is an independent consultant who writes for a number of publishers. She specializes in Access. "SQL: Access to SQL Server", her latest book, was released in January 2002.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date