Browse DevX
Sign up for e-mail newsletters from DevX


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

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




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:

  1. You want to inhibit physical deletes in the Products table.
  2. 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.)
  3. 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 vNormalRows AS SELECT * FROM Products WHERE IsDeleted IS NULL

    Similarly, you could create a view that exposes only the deleted rows:

    CREATE VIEW vDeletedRows AS SELECT * 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 Trigger CREATE 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

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