RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Use SQL Server Triggers to Track Unauthorized Database Changes

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

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.

Triggers Can Save Your Weekend
Suppose your boss calls an emergency meeting late on a Friday afternoon and tells you that product prices are being changed and deleted without authorization. New orders are being generated with incorrect pricing. Your boss wants you to find the unauthorized user—yesterday.

You remind your boss that the database system doesn't have any stealth forensic technology, and furthermore, management dismissed your proposed security and auditing process as unnecessary and too costly during the design and development phase. (Actually, skip the furthermore part.) Your boss expresses regret over your planned weekend canoe trip, which you'll almost certainly have to cancel, but the bottom line is you must determine who's making the unauthorized changes right away.

A trigger is a special kind of stored procedure that SQL Server offers. It might just get the job done quickly and efficiently—while you're on your canoe trip. A trigger is a predefined response to a specific data modification. In SQL Server terms, it refers to an UPDATE, INSERT, or DELETE action. Table 1 defines the two types of triggers.

Table 1: Types of SQL Server Triggers

Trigger Description Executed Against Number Allowed
FOR Executes after the triggering statement is completed Tables only Multiple FOR (also known as AFTER) triggers are allowed, and you can control which trigger fires first and last using the sp_settriggerorder. All other triggers fire in an undefined order, which you can't control.
INSTEAD OF Executes in place of the triggering action Tables and views Only one per table or view

The main benefit triggers offer is that they react automatically to a specific type of modification made to a specific table. Keep the following rules in mind when you're adding a trigger:

  • Only the table owner has permission to create triggers, and permission can't be transferred.
  • A trigger is considered a database object, so use object rules when naming and referencing a trigger.
  • Triggers are restricted to the current database, although you can reference an object outside the database.
  • A trigger can reference a temporary table but can't modify one.
  • A trigger can't reference a system table.

Complete the following three steps before you actually add a trigger:

  1. Identify the tables and views to which you'll add the trigger.
  2. Decide what action-UPDATE, DELETE, or INSERT-will fire the trigger.
  3. Choose a FOR or INSTEAD OF trigger.

To add a trigger, take the following steps:

  1. Open the Enterprise Manager and expand the appropriate nodes to locate the database to which you're adding the trigger.
  2. Click Tables (or Views).
  3. In the Details pane, right-click the appropriate table (or view).
  4. Select All Tasks from the resulting shortcut menu.
  5. Choose Manage Triggers (see Figure 1).
  6. Choose <new> from the Name dropdown list.
  7. Enter the trigger statement using the following syntax in the Text control:
    CREATE TRIGGER triggername
    ON table | view
    [WITH option]
     [IF UPDATE(column)]
     [AND | OR UPDATE(column)]
     [IF(COLUMNS_UPDATED() bitwise_operator updated_bitmask) 
    comparison operator column_bitmask [...n]] SQLstatement

Figure 1: Select Manage Triggers from the All Tasks Command

Author's Note: The CREATE TRIGGER statement is complicated. Defining all the arguments completely would complicate the current topic unnecessarily. You can find more in-depth coverage of this statement in Books Online, which comes with SQL Server 2000. But a few arguments are important to know for this article's technique. There are two types of triggers, FOR (or AFTER) and INSTEAD OF. FOR triggers execute immediately after the operation they mention. In other words, an insert trigger first executes the insert, then any code contained in the trigger. In contrast, an INSTEAD OF trigger executes its code instead of performing the related action.

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