Use SQL Server Triggers to Track Unauthorized Database Changes

SQL Server triggers are a powerful feature, yet they often take a backseat to stored procedures and user-defined functions—a fate they don't deserve. A trigger can provide an easy-to-implement solution to even the most complicated problem. One task in particular, tracking unauthorized changes in a database, can be transformed from a lengthy and difficult security task to a automated routine with the use of triggers. The secret is to store data about each change as it occurs, so you can determine who's changing data and when. The results of this tracking will help you determine whether a data change is a simple mistake in reporting, an unintentional mistake by an honest employee, or a real security hole that needs filling.

Knowing an error has occurred is fairly useless unless you can identify its source and prevent a recurrence. This article shows how triggers can serve as routine auditing procedures that track errors whether they're due to human error or program logic. You will learn how quickly triggers can help you track down errors or simply create a historical record of normal processes.