INSTEAD OF Triggers

`INSTEAD OF` Triggers

SQL Server 2000 introduced a new type of trigger called an INSTEAD OF trigger. This new trigger extends SQL Server's trigger capabilities and provides an alternative to the AFTER trigger that was heavily utilized in prior versions of SQL Server.

The name of the trigger gives you some insight into how this new trigger operates. The INSTEAD OF prefix is relevant because this particular trigger performs its actions instead of the action that fired it. This is much different from the AFTER trigger that will perform its actions after the statement that caused it to fire has completed. This means that you can have an INSTEAD OF UPDATE trigger on a table that successfully completes but does not include the actual update to the table. Take a look at the following example.

The basic syntax for creating an INSTEAD OF trigger is this:

 CREATE TRIGGER  trigger_name  ON  table_name  INSTEAD OF { INSERT  UPDATE  DELETE } AS  SQL statements

In Listing 29.11, you will create a trigger that prints a message stating the number of rows updated by an UPDATE statement. You will then execute an UPDATE against the table that has the trigger on it. Finally, you will select the rows from the table for review.

Listing 29.11 A Simple `INSTEAD OF` Trigger

 if exists (select * from sysobjects where id = object_id('dbo.cust_upd_orders')         and sysstat & 0xf = 8)         drop trigger dbo.cust_upd_orders GO CREATE TRIGGER trI_au_upd ON authors INSTEAD OF UPDATE AS PRINT 'TRIGGER OUTPUT: ' +CONVERT(VARCHAR(5), @@ROWCOUNT) + ' rows were updated.' GO UPDATE authors SET au_fname = 'Rachael' WHERE state = 'UT' GO TRIGGER OUTPUT: 2 rows were updated. SELECT au_fname, au_lname FROM authors WHERE state = 'UT' GO au_fname             au_lname                           -------------------- ---------------------------------------- Anne                 Ringer Albert               Ringer

As you can see from the results of the SELECT statement, the first name ( au_fname ) column was not updated to 'Rachael' . The update statement is correct, but the INSTEAD OF trigger did not apply the update from the statement as part of its INSTEAD OF action. The only action that the trigger did is to print its message.

The important point to realize is that after you define an INSTEAD OF trigger on a table, you need to include all of the logic in the trigger to perform the actual modification as well as any other actions that the trigger might need to carry out.

Execution

To gain a complete understanding of the INSTEAD OF trigger, you must also understand its execution in relation to the other events that are happening. The following list details the key event execution relative to the INSTEAD OF trigger firing:

Triggering Action ”The INSTEAD OF trigger fires instead of the triggering action. As shown earlier, the actions of the INSTEAD OF trigger replace the actions of the original data modification that fired the trigger.
Constraint Processing ”This action happens after the INSTEAD OF trigger fires. This includes check constraints, unique constraints, and primary key constraints.

To demonstrate the trigger execution order, refer to the following trigger in Listing 29.12.

Listing 29.12 `INSTEAD OF` Trigger Execution

 CREATE TRIGGER employee_insInstead ON employee INSTEAD OF insert AS DECLARE @job_id smallint --Insert the jobs record for the employee if it does not already exist IF NOT EXISTS (SELECT 1    FROM jobs j, inserted i   WHERE i.job_id = j.job_id) BEGIN    INSERT jobs        (job_desc, min_lvl, max_lvl)       SELECT 'Automatic Job Add', i.job_lvl, i.job_lvl        FROM inserted i --Capture the identify value for the job just inserted --This will be used for the employee insert later    SELECT @job_id = @@identity    PRINT 'NEW job_id ADDED FOR NEW EMPLOYEE:' + convert(char(3),@job_id) END --Execute the original insert action with the newly added job_id INSERT employee        (emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)    SELECT emp_id, fname, minit, lname, @job_id, job_lvl, pub_id, hire_date      FROM Inserted GO

This trigger can be created in the Pubs database that ships with SQL Server 2000. The key feature of this INSTEAD OF trigger is that it can satisfy a referential integrity constraint that was not satisfied before the INSERT was executed. Note the foreign key constraint on the employee table that references the job_id on the jobs table. The trigger first checks to see whether the jobs record associated with the job_id of the employee being inserted exists. If the jobs record does not exist for the inserted employee's job_id , then the trigger inserts a new jobs record and uses it for the insertion of the employee record.

If you execute the following INSERT statement, which has a job_id that does not exist, it will succeed:

 INSERT EMPLOYEE         (emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)    VALUES ('KNN33333F', 'Kayla', 'N', 'Nicole', 20, 100, 9952, getdate()) Go

That statement succeeds because the constraint processing happens after the INSTEAD O F trigger completes its actions. Conversely, if you were to create the same trigger as an AFTER trigger, the foreign key constraint would execute before the AFTER trigger and the following error message would be displayed:

 INSERT statement conflicted with COLUMN FOREIGN KEY constraint  'FK__employee__job_id__1BFD2C07'. The conflict occurred in database 'pubs', table 'jobs', column 'job_id'. -->The statement has been terminated.

Notice also, with the previous INSTEAD OF trigger example, that the last action that the trigger performs is the actual insertion of the employee record. The trigger was created to fire when an employee was inserted, so the trigger must perform the actual INSERT . This INSERT occurs in addition to any other actions that justify the trigger's creation.

`AFTER` Versus `INSTEAD OF` Triggers

Now that you have seen some of the key differences between the two types of triggers, you need to decide which trigger to use. In the previous example, the INSTEAD OF trigger was your only option for this kind of functionality. But, often, either trigger type can be used to attain the same result.

Something you should consider when making your choice is the efficiency of the overall modification. For example, if you have a modification that will cause a trigger to fire and often reject the modification, you might want to consider using the INSTEAD OF trigger. The rationale is that the INSTEAD OF trigger will not perform the actual modification until after the trigger completes. You will not need to undo the modification. If you were to use an AFTER trigger in the same scenario, any modifications that were rejected need to be rolled back because they have already been written to the transaction log by the time the AFTER trigger fires.

Conversely, if you have a situation in which the vast majority of the updates are not rejected, then the AFTER trigger might be your best choice. The particular situation will dictate the preferred type, but keep in mind that the INSTEAD OF triggers tend to be more involved. This is driven by the fact that you can only have one INSTEAD OF trigger on a table, and that trigger must perform the actual data modification that fired it.

`AFTER` and `INSTEAD OF` Triggers

One other consideration when coding INSTEAD OF triggers is that they can exist on the same table as an AFTER trigger. INSTEAD OF triggers can also execute based on the same data modifications.

Take, for example, the previous INSTEAD OF trigger that was placed on the employee table in the Pubs database that ships with SQL Server 2000. An AFTER trigger exists on the employee table by default (see Listing 29.13).

Listing 29.13 `AFTER` Trigger Placed on the Same Table as an `INSTEAD OF` Trigger

 if exists (select * from sysobjects where id = object_id('dbo.employee_insupd')         and sysstat & 0xf = 8)         drop trigger dbo.employee_insupd GO CREATE TRIGGER employee_insupd ON employee FOR INSERT, UPDATE AS --Get the range of level for this job type from the jobs table. declare @min_lvl tinyint,    @max_lvl tinyint,    @emp_lvl tinyint,    @job_id smallint select @min_lvl = min_lvl,    @max_lvl = max_lvl,    @emp_lvl = i.job_lvl,    @job_id = i.job_id from employee e, jobs j, inserted i where e.emp_id = i.emp_id AND i.job_id = j.job_id IF (@job_id = 1) and (@emp_lvl <> 10) begin    raiserror ('Job id 1 expects the default level of 10.',16,1)    ROLLBACK TRANSACTION end ELSE IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl) begin    raiserror ('The level for job_id:%d should be between %d and %d.',       16, 1, @job_id, @min_lvl, @max_lvl)    ROLLBACK TRANSACTION End go

This AFTER trigger checks whether the job level assigned to the employee falls within a valid range for the job_id that the employee was assigned. It is fired for both inserts and updates, and it can exist on the same table as the employee_insInstead INSTEAD OF trigger that was described earlier. The combined effect on an employee INSERT (with both the triggers on the employee table) is to have the following actions happen in the order listed:

The INSERT data modification is executed.
The INSTEAD OF trigger fires, completes its validation, and ultimately does the employee INSERT that is written to the transaction log.
Constraint Processing completes.
The AFTER trigger fires performing its actions on the employee record that was inserted by the INSTEAD OF trigger.
The AFTER trigger completes and commits the transaction to the database.

One of the key points in this example is that the AFTER trigger performs its actions on the row inserted by the INSTEAD OF trigger. It does not use the record from the original INSERT that started the trigger execution. Therefore, in this chapter's example, where the INSTEAD OF trigger generates a new job_id , the new job_id value is used in the AFTER trigger, not the job_id that was originally inserted.

Rollback and recovery need to be considered in this scenario as well, but it is beyond the scope of this discussion. This example simply shows you that these two types of triggers can be combined, and that the order of execution needs to be considered when designing your trigger solution.

Views with `INSTEAD OF` Triggers

One of the most powerful applications of an INSTEAD OF trigger is to a View. The INSTEAD OF trigger, unlike the AFTER trigger, can be applied to a View and triggered based on modifications to it. For more information on Views, see Chapter 27, "Creating and Managing Views in SQL Server."

The reason that this is so important is because data modifications have many restrictions when made via a View. The list is extensive , but following are a few examples:

You cannot use data modification statements that apply to more than one table in the View in a single statement.
All columns defined as NOT NULL in the underlying tables that are being updated must have the column values specified in the modification statement.
If the View was defined with the WITH CHECK OPTION clause, then rows cannot be modified in a way that will cause them to disappear from the View.

The INSTEAD OF trigger is a mechanism that can be used to overcome some of these restrictions. In particular, the first restriction ( related to a single table modification) can be addressed with the INSTEAD OF trigger. The INSTEAD OF trigger fires before the actual modification takes place so it can resolve the modifications to the underlying tables associated with the View. It can then execute the modification directly against those base tables. This capability is demonstrated in the following example:

 Use PUBS  go CREATE VIEW employeeJobs AS select j.min_lvl, j.max_lvl, j.job_id, j.job_desc, e.job_lvl, e.emp_id  from employee e, jobs j where e.job_id = j.job_id GO

This creates a View in the Pubs database that joins data from the employee and jobs tables. It retrieves the job types and the associated levels, the employees assigned to the job types, and the employee's current job level. A sample set of rows from the view are shown next :

 min_lvl max_lvl job_id job_desc                               job_lvl emp_id  ------- ------- ------ -------------------------------------- ------- --------- 25      100     14     Designer                               35      ENL44273F 25      100     14     Designer                               89      PSA89086M 25      100     14     Designer                               100     KFJ64308F 25      100     12     Editor                                 32      Y-L77953M 25      100     12     Editor                                 35      H-B39728F 25      100     12     Editor                                 100     HAS54740M

Perhaps you want to change the minimum job level ( min_lvl ) for the Designer job to 40 and at the same time set the job level ( job_lvl ) for any employees who have this job to 40 as well. If you execute the following update ”without an INSTEAD OF trigger ”against the View, you get the message shown:

 UPDATE employeeJobs     SET min_lvl = 40,        job_lvl = 40  WHERE job_id = 12 GO View or function 'employeeJobs' is not updateable because the modification affects multiple base tables.

To get around this problem, you can use an INSTEAD OF trigger. The trigger can decipher the update to the View and apply the updates to the base table without receiving the error. This functionality is demonstrated in the INSTEAD OF trigger found in Listing 29.14.

Listing 29.14 Basic View with an `INSTEAD OF` Trigger

 CREATE TRIGGER employeeJobs_updInstead ON employeeJobs INSTEAD OF UPDATE AS IF @@ROWCOUNT = 0 RETURN --update the data related to the jobs table UPDATE jobs    SET jobs.min_lvl = i.min_lvl,        jobs.max_lvl = i.max_lvl,        jobs.job_desc = i.job_desc   FROM inserted i  WHERE jobs.job_id = i.job_id    AND (jobs.min_lvl <> i.min_lvl        OR jobs.max_lvl <> i.max_lvl        OR jobs.job_desc <> i.job_desc) --update the data related to the jobs table UPDATE employee    SET employee.job_lvl = i.min_lvl   FROM inserted i  WHERE employee.emp_id = i.emp_id GO

This example has a section that checks the fields related to the jobs table and updates the base table if any of the values have changed. It also has a section that updates the employee table for the employee fields that have been changed in the View.

NOTE

This trigger could be enhanced to include logic to check for specific updates or to update only those employees who are assigned to the job and have a job level below the new minimum. These enhancements were not added to keep the example simple.

If you now execute the same update statement, you don't get an error message. You get the results shown here:

 UPDATE employeeJobs     SET min_lvl = 40,        job_lvl = 40  WHERE job_id = 12 GO min_lvl max_lvl job_id job_desc                               job_lvl emp_id ------- ------- ------ -------------------------------------- ------- --------- 25      100     14     Designer                               35      ENL44273F 25      100     14     Designer                               89      PSA89086M 25      100     14     Designer                               100     KFJ64308F 25      100     13     Sales Representative                   35      PMA42628M 25      100     13     Sales Representative                   64      CGS88322F 25      100     13     Sales Representative                   100     TPO55093M 40      100     12     Editor                                 40      Y-L77953M 40      100     12     Editor                                 40      H-B39728F 40      100     12     Editor                                 40      HAS54740M

Notice that the Editor job now has a minimum level ( min_lvl ) equal to 40, and that all of the employees who have that job level ( job_lvl ) are also set to 40.

You can see the added flexibility that you get by using the INSTEAD OF trigger on a basic View. This flexibility is also applicable to a more sophisticated View called a Distributed Partitioned View. With this type of View, data for the View can be partitioned across different servers. This gives you the ability to scale your database solution and still have a single view of the data that appears as one table.

You can make data modifications via a Distributed Partitioned View, but some restrictions exist. In the case in which the requirements are not met for updating the View, the INSTEAD OF trigger can be used to bypass these restrictions; this is similar to the previous example.

For a more in-depth discussion of Distributed Partitioned Views, see Chapter 21, "Administering Very Large SQL Server Databases."

`INSTEAD OF` Trigger Restrictions

You have seen many of the capabilities of INSTEAD OF triggers, but they also have limitations. The following list shows you some of them:

INSTEAD OF triggers do not support recursion. This means that they cannot call themselves regardless of the setting of the Recursive Triggers database option. For example, if an INSERT is executed on a table that has an INSTEAD OF trigger and the INSTEAD OF trigger performs an INSERT on this same table, then the INSTEAD OF trigger for this INSERT will not fire for a second time. Any AFTER triggers defined on the same table for INSERT will fire based on the INSTEAD OF trigger INSERT .
You can define only one INSTEAD OF trigger for each action on a given table. Therefore, you can have a maximum of three INSTEAD OF triggers for each table: one for INSERT , one for UPDATE , and one for DELETE .
A table cannot have an INSTEAD OF trigger and a foreign key constraint with CASCADE defined for the same action. For example, you cannot have an INSTEAD OF trigger defined for delete on a given table as well as a foreign key with a CASCADE DELETE definition. You will receive an error if you attempt to do this. In this situation, you could have INSTEAD OF triggers defined on INSERT and UPDATE without receiving an error.