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 Table
WHERE id = object_id(N'dbo.AuditTrail')
OBJECTPROPERTY(id, N'IsUserTable') = 1
DROP TABLE dbo.AuditTrail
CREATE 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
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.
Table 2: AuditTrail Table
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.
- Choose <new> 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
INSERT INTO AuditTrail (TableName, ActionTaken,
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:
SET UnitPrice = 1
WHERE ProductID = 1
- Run the following SQL statement to see if the trigger worked. Figure 3 shows the results.
|Figure 3: Updating the Products Table Fires the Table's Trigger|