Professional SQL Server™ 2005 Programming

13.5. INSTEAD OF Triggers

While it can work against tables, the primary purpose of an INSTEAD OF trigger is usually to allow updates to views in places where it was previously not possible.

Essentially, an INSTEAD OF trigger is a block of code we can use as something of an interceptor for anything that anyone tries to do to our table or view. We can either elect to go ahead and do whatever the user requests or, if we choose, we can go so far as doing something that is entirely different.

As with FOR/AFTER triggers, INSTEAD OF triggers come in three different flavors — INSERT, UPDATE, and DELETE. Unlike FOR/AFTER triggers, however, you can only have one trigger per table or view for each of the different flavors (one each for INSERT, UPDATE, DELETE).

If we're going to explore these, we need to get some appropriate sample tables out there. To that end, let's take the follow four tables (you can change the script to use an existing database if you wish).

CREATE TABLE dbo.Customers
    (
    CustomerID varchar(5) NOT NULL PRIMARY KEY ,
    Name varchar(40) NOT NULL
    )

CREATE TABLE dbo.Orders
     (
     OrderID int IDENTITY NOT NULL PRIMARY KEY,
     CustomerID varchar(5) NOT NULL
         REFERENCES Customers(CustomerID),
     OrderDate datetime NOT NULL
     )

CREATE TABLE dbo.Products

( ProductID int IDENTITY NOT NULL PRIMARY KEY, Name varchar(40) NOT NULL, UnitPrice money NOT NULL ) CREATE TABLE dbo.OrderItems ( OrderID int NOT NULL REFERENCES dbo.Orders(OrderID), ProductID int NOT NULL REFERENCES dbo.Products(ProductID), ...