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 vNormalRows
WHERE IsDeleted IS NULL
Similarly, you could create a view that exposes only the deleted rows:
CREATE VIEW vDeletedRows
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
SET IsDeleted = 1
WHERE AuditTrailID = (SELECT AuditTrailID
INSERT INTO AuditTrail(TableName, ActionTaken,
ActionUser, ActionDate) VALUES
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.