'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 OF 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.
NOTEThis 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.
