INSTEAD OF Triggers on Views

Get more flexibility to validate modifications and update views

Last month, I talked about a new SQL Server 2000 feature called INSTEAD OF triggers. These triggers let you define a routine to execute in lieu of a particular data-manipulation operation. I showed you a routine to check whether a price change in the Titles table in the Pubs database met certain requirements. For this check, you could write an AFTER trigger (the default trigger type) that would compare the old and new prices and invoke a rollback if the change wasn't acceptable, or you could write an INSTEAD OF trigger. An INSTEAD OF trigger fires before a change takes place, so if the change is unacceptable, no more work needs to take place. However, if the change is allowable, the INSTEAD OF trigger must issue a reconstructed data-modification statement to perform the modification. As I mentioned, in some situations, you can write either an INSTEAD OF trigger or an AFTER trigger to perform the same function, but the real purpose—and power—of INSTEAD OF triggers is to let you update certain views that usually aren't updateable. In fact, INSTEAD OF triggers are the only trigger type you can have on any type of view. For example, you usually can't execute DELETE operations on a view that is based on a join. However, you can write an INSTEAD OF DELETE trigger for such a view. Because the trigger has access to the view rows that would have been deleted had the view been a real table, it can use that information to update the view's underlying tables. As I mentioned last month, the deleted rows are available in the Deleted table. Similarly, in an INSTEAD OF UPDATE or INSTEAD OF INSERT trigger, you can access the new rows through the Inserted table.

When Is a View Updateable?

According to SQL Server 2000 Books Online (BOL), a view is updateable only if it satisfies the following conditions:

The data-modification statements themselves—that is, the UPDATE, INSERT, and DELETE statements—also must meet certain conditions before they can reference a view that fulfills the updateability requirements. UPDATE and INSERT statements can reference a view only if the view is updateable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the view's FROM clause. A DELETE statement can reference an updateable view only if the view references exactly one table in its FROM clause. Microsoft imposed these restrictions because of a basic SQL requirement that any one data-modification statement can modify only one table. A DELETE statement that deletes rows from a view that is based on a join of two tables would need to delete from both tables, an action that SQL doesn't allow.

Listing 1, page 36, shows a simple code example that creates Table1 and Table2, then builds a view on a join of those tables. In this case, the view contains values from each table. An INSTEAD OF trigger can use these values to determine which rows in the base table need to be removed when someone executes a DELETE statement on the view. The rows deleted from the view are available in the Deleted table inside the trigger, so the trigger has all the information it needs to generate the DELETE statements for both base tables.

UNION Views

Another kind of view that has severe updateability limitations is a view based on a UNION. Some UNION views, such as views that are partitioned, are updateable; for more information about these views, see "Modifying Views with INSTEAD OF Triggers." You might not be able to directly update views that don't meet the conditions for partitioning. However, you can define INSTEAD OF triggers on these non-updateable UNION views so you can update them.

Using the code in Listing 2, you can create a contacts list view consisting of the name, city, state, and country of all authors, stores, and publishers in the Pubs database. Then you can create an INSTEAD OF INSERT trigger that will let you insert a new contact into this list. The Inserted table in the trigger will have values for only the columns in the view, so all other columns in all three tables need to have default values or allow NULLs, or this operation will fail. The only column that doesn't meet this requirement is the contract column in the Authors table, which is a bit column. You need to either alter the column to let it accept NULL values or alter the column to add a default constraint. To let the contract column accept NULL values, use the following code:

ALTER TABLE authors   ALTER column contract bit NULL

To add a default constraint to the contract column, use the following code:

ALTER TABLE authors   ADD CONSTRAINT contract_dflt DEFAULT 0 FOR contract

The trigger needs to determine which base table (Authors, Stores, or Publishers) should accept a row that you insert into the contact_list view. Author ID values look different from store ID or publisher ID values. So by looking for a hyphen, which occurs only in an author ID value, SQL Server can determine whether a new row refers to an author. To distinguish stores from publishers, you can use the Publishers table's check constraint, which requires new publisher ID values to start with the digits 99. (You can execute sp_helpconstraint on the Publishers table to see this constraint.) If you use this constraint to identify publishers, no stores can have an ID that starts with 99. If you can't limit your store ID options, you might need a solution other than this INSTEAD OF trigger.

The trickiest part of this trigger is that the view has concatenated the authors' first and last names into a single name column. I combined names because the Stores and Publishers tables each have only one column for the name. So, when it inserts a row into the Authors table, the INSTEAD OF trigger must separate a single name value into a two-part name. The trigger uses the simple approach of looking for the first space in the name, then takes everything before the first space as the first name and everything after the first space as the last name. Listing 3 shows the Insert_Contact INSTEAD OF INSERT trigger, which determines which of the three tables needs to be modified, then issues the appropriate INSERT statement. Of course, this trigger should probably contain a lot more error checking to make sure that the INSERT statements meet any other requirements the tables have. You could write similar triggers for updates and deletes on the contact_list view.

To test the trigger, you can run the following sample INSERT statement:

INSERT INTO contact_list VALUES ('7777', 'A Brand New Store', 'Poulsbo', 'WA', 'USA')

After executing the INSERT, you can look at the values in the Stores table and see that SQL Server has added the new data. You can also use SQL Profiler to watch the statements that SQL Server is executing. From the Profiler's perspective, a trigger (either an INSTEAD OF or an AFTER trigger) is just like a stored procedure. So when defining the events to trace with SQL Profiler, you need to make sure you capture the event—either SP:StmtStarting or SP:StmtCompleted—for statements within stored procedures. When I traced the above INSERT statement, SQL Profiler showed the following statements as having executed within the trigger:

-- Insert_Contact CREATE TRIGGER Insert_Contact ON contact_listINSTEAD OF INSERT AS-- Insert_Contact IF @@rowcount = 0-- Insert_Contact IF (select count(*) FROM Inserted) > 1-- Insert_Contact IF (SELECT substring(ID,4,1) FROM inserted) = '-'-- Inserting an author-- Insert_Contact IF (SELECT ID FROM inserted) like '99[0-9][0-9]' -- Inserting a publisher-- Insert_Contact INSERT INTO stores(stor_id, stor_name, city, state)    SELECT id, name, city, state FROM inserted-- Insert_Contact RETURN

Managing Triggers

The syscomments system table holds the text of both INSTEAD OF triggers and AFTER triggers; you can use the system stored procedure sp_helptext to view the trigger text unless the trigger is encrypted. The OBJECTPROPERTY function can also return information about triggers and about tables or views that have triggers. Here are a few examples of how you can use the OBJECTPROPERTY function to retrieve information about your triggers:

SELECT OBJECTPROPERTY (object_id('