This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

SQL Server 2000's INSTEAD OF Triggers

Tom Moreau

SQL Server 2000 introduces INSTEAD OF triggers. They might not solve all of your problems, but they can help you in some weird situations. And did you know that they're also permitted on views? Tom Moreau explains.

Okay, so I thought SQL Server 2000 was going to have some extra features that 7.0 didn't have, sort of like going from 6.0 to 6.5. Well, SQL Server 2000 is to 7.0 what 7.0 was to 6.5. This release is just jammed with features, and one of them is INSTEAD OF triggers. I'll first briefly review what triggers are and then dive into how INSTEAD OF triggers can better your life.

Triggers have been with us since the Sybase days. They're pieces of code that look at lot like stored procedures. However, triggers are attached to tables (and, beginning with SQL Server 2000, to views as well) and act only when the appropriate action (such as INSERT, UPDATE, or DELETE) for which the trigger has been set up has been executed. You can't fire a trigger directly—only the triggering action can do that.

	Before SQL Server 2000 was released, the only type of trigger was the FOR trigger, now called an AFTER trigger. This type of trigger fires after the triggering action and after all constraints have been successfully validated. Since cascading DELETEs and UPDATEs weren't provided through declarative RI until the release of SQL Server 2000, the only way to do this nicely was to use triggers, since any referential integrity constraints would restrict the DELETE or UPDATE. Sure, you could also do this in a stored procedure, but you'd have to ensure that every stored procedure that would need to do an UPDATE/DELETE on a "parent" table would first do the same operation on the "child" table.

	What makes an INSTEAD OF trigger so special? This type of trigger intercepts the triggering action. It populates SQL Server's special inserted and deleted virtual tables in the same manner as would happen with an AFTER trigger. However, the data modifications don't actually take place on the underlying table or view. The way I see things, this gives us the ability to do the INSERT, UPDATE, or DELETE the way we want it. Indeed, if you don't modify the data with your trigger, the data will remain intact.

Implementing logical deletes

Suppose you want to do a logical DELETE instead of a physical DELETE. Thus, every DELETE made against the table would set the Deleted column to 'Y.' This can be handled through an INSTEAD OF trigger, as the following code shows:

  CREATE TRIGGER trd_MyTable on MyTable
INSTEAD OF DELETE
AS
IF @@ROWCOUNT = 0
  RETURN
UPDATE m
SET
  Deleted = 'Y'
FROM
     MyTable m
JOIN deleted d ON d.PK_ID = m.PK_ID
go

What if you really wanted the rows to be logically deleted during the month but physically deleted at the end of the month? Simple—just disable the trigger with an ALTER TABLE statement and then do the DELETE, as shown here:

  ALTER TABLE MyTable DISABLE TRIGGER trd_MyTable
go
DELETE MyTable
WHERE
  Deleted = 'Y'
go

	Just don't forget to re-enable the trigger when you're done!

Implementing vertical partitioning

As stated in the introduction, you can use INSTEAD OF triggers on views. (AFTER triggers aren't permitted on views.) The beauty of this is that you can hide the "physicalization" of your database and allow your developers to touch the views while your INSTEAD OF trigger handles the nuts and bolts.

	Here's an example. Suppose you've implemented vertical partitioning of a logical table into two separate, physical tables, each with the same primary key and a 1:1 relationship between the two. You put your most frequently used columns in one table and the least frequently used columns in another. Here's the DDL code:

  CREATE TABLE FirstHalf
(
  EmployeeID int NOT NULL PRIMARY KEY
, PhoneNo char (10) NOT NULL
)
go
CREATE TABLE SecondHalf
(
  EmployeeID int NOT NULL PRIMARY KEY REFERENCES
  FirstHalf (EmployeedID)
  , Address varchar (50) NOT NULL
  , City varchar (25) NOT NULL
)
go
CREATE VIEW WholeThing
AS
SELECT
  f.EmployeeID
, f.PhoneNo
, s.Address
, s.City
FROM
     FirstHalf  f
JOIN SecondHalf s ON f.EmployeeID = s.EmployeeID
go

	Now, instead of having a single physical table for employee information, you have two, and you represent it logically as a single view with a join between them. If you go to INSERT into the view, you'll need an INSTEAD OF trigger to handle the split between the two base tables. Otherwise, the INSERT will fail. This is done in the following code:

  CREATE TRIGGER tri_WholeThing ON WholeThing 
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
  RETURN
INSERT FirstHalf
SELECT
  EmployeeID
, PhoneNo
FROM
  inserted
INSERT SecondHalf
SELECT
  EmployeeID
, Address
, City
FROM
  inserted
go

	Because you're inside an implicit transaction, you don't need a BEGIN TRAN/COMMIT TRAN inside the trigger code. Bear in mind, however, that you should do some error checking for each INSERT. Any INSERTs on the WholeThing view are now split into one INSERT into the FirstHalf table and another into the SecondHalf table. You can create similar ones for UPDATE and DELETE. Your trigger code must respect the fact that there's a foreign key between the two tables. Therefore, you must insert into the FirstHalf table before inserting into the SecondHalf table.

Insert or update?

One classic problem that DBAs often face is one where you're inserting data into a table and you don't want to have a primary key violation if the key already exists. Instead, you'd like the existing data for that key to be updated. If, however, the key doesn't already exist, you want the row to be inserted. How do you use INSTEAD OF triggers to deal with this problem? The following code does it for you.

  CREATE TABLE FeedTarget
(
  ID int NOT NULL PRIMARY KEY,
  Descr char (5) NOT NULL
)
GO
CREATE TRIGGER tri_FeedTarget ON FeedTarget
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
  RETURN
UPDATE f         -- rows that already exist
SET
  Descr = i.Descr
FROM
    inserted   i
  JOIN
    FeedTarget f ON f.ID = i.ID
INSERT FeedTarget    -- new rows
SELECT
  ID,
  Descr
FROM
    inserted i
WHERE NOT EXISTS
(
  SELECT
  FROM
      FeedTarget f
  WHERE
      f.ID = i.ID
)
go

The trigger first updates any existing rows by joining the inserted table with the base table—FeedTarget. It then does the actual INSERT through a correlated subquery that uses a WHERE NOT EXISTS predicate to ensure that no rows for the incoming keys already exist.

Restrictions

Anything good usually comes with some restrictions, and INSTEAD OF triggers are no exception. While you may have more than one FOR (now AFTER) trigger per triggering action, you may have only one INSTEAD OF trigger per triggering action on a given object. You can fake this, however, by creating a stack of views with each view being a SELECT * on the next view until the final view is a SELECT * of the base object. You then attach the INSTEAD OF triggers on each of the views, and all access is done through the topmost view.

	There are also restrictions with respect to the use of INSTEAD OF triggers where cascaded DELETEs or UPDATEs have been put in place through DRI (declarative referential integrity). You can't have an INSTEAD OF trigger on a table with the corresponding CASCADE action, so there can be no INSTEAD OF DELETE when ON DELETE CASCADE is in effect. In other words, if you've created a table that has a FOREIGN KEY constraint with ON DELETE CASCADE, you can't create an INSTEAD OF DELETE trigger on that same table. The same restriction applies to INSTEAD OF UPDATE triggers and cascaded UPDATEs. If you attempt to create the trigger when the CASCADE option is in effect, the CREATE TRIGGER statement will fail. If you create the trigger first and then alter the table to add the FOREIGN KEY constraint with the CASCADE option, the ALTER TABLE statement will fail.

Conclusion

INSTEAD OF triggers are something I didn't realize I needed until Microsoft provided them. Now, they're a "must-have." I've presented just a few examples of how they can be used to solve practical problems; I'm sure you can come up with some yourself. Shameless plug: I cover INSTEAD OF triggers in more detail in Advanced Transact-SQL for SQL Server 2000 (Apress).

Download INSTEAD.SQL

To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the January 2001 issue of Microsoft SQL Server Professional. Copyright 2001, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.