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.
|Executes after the triggering statement is completed
|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.
|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:
- Identify the tables and views to which you’ll add the trigger.
- Decide what action-UPDATE, DELETE, or INSERT-will fire the trigger.
- Choose a FOR or INSTEAD OF trigger.
To add a trigger, take the following steps:
- Open the Enterprise Manager and expand the appropriate nodes to locate the database to which you’re adding the trigger.
- Click Tables (or Views).
- In the Details pane, right-click the appropriate table (or view).
- Select All Tasks from the resulting shortcut menu.
- Choose Manage Triggers (see Figure 1).
from the Name dropdown list.
- Enter the trigger statement using the following syntax in the Text control:
CREATE TRIGGER triggernameON table | view[WITH option]FOR | AFTER | INSTEAD OF [UPDATE[, DELETE[, INSERT]]]AS [IF UPDATE(column)] [AND | OR UPDATE(column)] [...n] [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. Trigger an Auditing Solution
Finding the unauthorized user can be a simple task for SQL Server triggers. The first thing you need is a table to store all the audit data. The table should point to all actions by all users, including unauthorized users and unauthorized changes. Then you add triggers that react to each type of possible modification action-an INSERT, an UPDATE, or a DELETE statement. To create a table in which you can store this new audit data, open the Query Analyzer, specify the database in the dropdown database control, and then run the SQL statement in Listing 1.
Listing 1: Create Audit TableIf EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.AuditTrail') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )DROP TABLE dbo.AuditTrailGOCREATE TABLE dbo.AuditTrail ( AuditTrailID Int IDENTITY (1, 1) NOT NULL, TableName VarChar (50) NOT NULL, ActionTaken Char (1) NOT NULL, ActionUser VarChar (50) NOT NULL, ActionDate DateTime NOT NULL ) ON [PRIMARY]GO
You’ll use the Northwind sample database that comes with SQL Server in this example, but you can use any database you like. This procedure deletes a table named AuditTrail, if found, and creates a new table named AuditTrail with the attributes shown in Table 2. If you think you’ll need to restart the process on a regular basis, use a stored procedure or a user-defined function. You must surround the PRIMARY keyword with brackets, and remember to include brackets around any of your object names if they include space characters.
Now you’re ready to add the actual triggers. (In this example, you won’t attempt to create or even describe a full-featured audit trail. You use the audit trail as an example of using triggers to update a file when data changes.) You’ll create two triggers, one that responds to an INSERT or an UPDATE, and one that responds to a DELETE. Begin with the INSERT and UPDATE trigger as follows:
- Expand the Enterprise Manager to the Northwind database and double-click the Tables icon.
- Right-click the Products table, choose All Tasks, and select Manage Triggers.
from the Name dropdown control.
- Enter the following trigger in the Text control (see Figure 2):
CREATE TRIGGER [AuditInsertUpdate] ON dbo.Products FOR INSERT, UPDATE AS INSERT INTO AuditTrail (TableName, ActionTaken,
ActionUser, ActionDate) VALUES ('Products', 'I', User_Name(), GetDate())
- Click Check Syntax and make corrections to the trigger statement if necessary.
- Click Apply when the trigger is correct, and then click Close.
|Figure 2: Enter the INSERT and UPDATE Trigger
SQL Server will allow the user to insert and update new records. After which, the above triggers will modify the AuditTrail table by entering the following:
- The name of the table modified into the TableName column
- The letter I (for Insert) or U (for Update) into the ActionTaken column
- The results of the User_Name() function at the time the change is executed into the ActionUser column
- The results of the GetDate() functions at the time the change is executed into the ActionDate column
Now, test the trigger by changing the UnitPrice entry for one of the products in the Northwind Products table. To do so, take the following steps:
- Open the Query Analyzer, and specify Northwind in the database dropdown control.
- Run the following SQL statement to reduce the current value of the product identified as 1 from $18 to $1:
UPDATE dbo.ProductsSET UnitPrice = 1WHERE ProductID = 1
- Run the following SQL statement to see if the trigger worked. Figure 3 shows the results.
SELECT *FROM dbo.AuditTrail
|Figure 3: Updating the Products Table Fires the Table’s Trigger
The Advantages of INSTEAD OF Triggers
You can write a trigger for a view, but if the view is updateable it isn’t necessary. Triggers on the underlying table fire automatically. (Of course, you may have your own reasons why you want triggers on such views.) Of all the advantages INSTEAD OF triggers offer, the main one is that they allow views that would normally not be updateable to support updates. A view that involves multiple tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table. For example, you can write an INSTEAD OF trigger that inserts rows in multiple tables from a single view.
Another important advantage to INSTEAD OF triggers is that they allow you to write logic that accepts parts of a batch while rejecting other parts. Finally, INSTEAD OF triggers allow you to take some alternative action in the event of some particular condition that the application defines as an error.
Our example INSTEAD OF trigger assumes the following requirements:
- You want to inhibit physical deletes in the Products table.
- You want to emulate delete actions by adding an IsDeleted column to the Products table and then intercepting DELETE actions. In lieu of actually performing the delete, you want to change the value of that record’s corresponding IsDeleted value to True (1). You’ll have to add a new column to Products to accommodate this trigger’s goal. (If you’re actually working with the Products table, you should work with a copy of the table instead of the original table.)
- To support this implementation, you would of course need some views or stored procedures that screen out the “deleted” rows. Using Northwind’s Products table, you might use a view that resembles one of the following:
CREATE VIEW vNormalRowsASSELECT * FROM Products WHERE IsDeleted IS NULL
Similarly, you could create a view that exposes only the deleted rows:
CREATE VIEW vDeletedRowsASSELECT * FROM Products WHERE IsDeleted = 1
Any forms your front end uses should be based on one of these views. In short, you’ve enabled a pseudo undelete command. When the user deletes a row, the trigger changes the value of that record’s IsDeleted value to 1, INSTEAD OF actually deleting the row. In addition, the trigger also documents the DELETE attempt in the AuditTrail table.
To try this trigger yourself, modify the Products table in the Northwind database by adding a nullable bit column named IsDeleted. Then, using the instructions given earlier, add the INSTEAD OF trigger in Listing 2.
Listing 2: Delete TriggerCREATE Trigger AuditDeleteInstead ON dbo.Products INSTEAD OF DELETE AS UPDATE Products SET IsDeleted = 1 WHERE AuditTrailID = (SELECT AuditTrailID FROM Deleted) INSERT INTO AuditTrail(TableName, ActionTaken, ActionUser, ActionDate) VALUES ('Products','D', User_Name(),GetDate())
For Access Users
If you’re working in an Access project (an .adp file), open the Products table, select any row(s), and then press Deleted. The row seems to disappear. Refresh the display by pressing F9 and the row magically reappears, but now the value of IsDeleted is True (see Figure 4). The INSTEAD OF trigger ignores the original DELETE request, marks the IsDeleted column, and then documents the action in the AuditTrail table.
|Figure 4: The INSTEAD OF Trigger
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 ColumnCREATE 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.
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.