INSTEAD-OF trigger

A trigger is a database object similar to a stored procedure that executes in response to certain actions that occur in your database environment. SQL Server 2005 is packaged with three flavors of trigger objects: AFTER, data definition language (DDL), and INSTEAD-OF.
AFTER triggers are stored procedures that occur after a data manipulation statement has occurred in the database, such as a delete statement. DDL triggers are new to SQL Server 2005, and allow you to respond to object definition level events that occur in the database engine, such as a DROP TABLE statement. INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.

Why use an INSTEAD-OF trigger?

INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.
Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.

An example

To better illustrate the idea of an updateable view, it’s always great to use an example. In this example, I refer to a fictitious scenario that includes a Products lookup table and a Purchases table, which records those instances where products are purchased. Shown below Listing A contains the script to create these tables. After running the script to create the tables I will use in the example, I will run the script in Listing B to insert some data into the tables.
Now that the sample tables have data in them, I can create a view to join these tables and present the data in a meaningful way. Check out Listing C.
This is a pretty typical production-level view. It joins two tables in the database structure, which greatly simplifies data retrieval. However, the data abstraction provided is not the only advantage of using views. Attaching INSTEAD-OF trigger(s) to this view allows me to modify the underlying tables, so that I may never need to modify the data in the underlying tables directly. I’ll use the script in Listing D to create an INSTEAD-OF trigger on the vw_ProductPurchases view.
Notice that in the trigger declaration I specify the INSTEAD OF clause. Triggers created in SQL Server are AFTER triggers by default, so I must specify the INSTEAD OF clause in the trigger definition.
The first statement in the trigger is a “check” statement. Here I am checking the INSERTED table to ensure that the ProductID is present, and that either the PurchasePrice or the ProductPrice has been provided.
If the necessary data has been inserted into the view via an INSERT statement, the trigger will insert the specified values into the underlying data table. This is what a sample INSERT statement into the view would look like.

INSERT INTO vw_ProductPurchases(ProductID, PurchasePrice) VALUES(1, 700)

This INSERT statement provides a valid ProductID and PurchasePrice, which means a new record will be inserted into the Purchases table.

Conclusion

With a bit of imagination, it is easy to see the power and flexibility provided by INSTEAD-OF triggers. If your system is not extremely large, using a system of views to abstract your underlying database schema can provide a great way to shield your database programmers from modifying the data in the underlying tables directly.
Listing A:
CREATE TABLE Products
(
ProductID SMALLINT IDENTITY(1,1) PRIMARY KEY,
Description VARCHAR(75),
Price MONEY NOT NULL
)
GO
CREATE TABLE Purchases
(
PurchaseID SMALLINT IDENTITY(1,1) PRIMARY KEY,
ProductID SMALLINT REFERENCES Products(ProductID),
PurchasePrice MONEY NOT NULL,
PurchaseDate SMALLDATETIME DEFAULT(GETDATE())
)
Listing B:

INSERT INTO Products(Description, Price) VALUES('Television',500)

INSERT INTO Products(Description, Price) VALUES('VCR',100)

INSERT INTO Products(Description, Price) VALUES('DVD_Player',125)

INSERT INTO Products(Description, Price) VALUES('Alarm_Clock',40)

INSERT INTO Products(Description, Price) VALUES('Camera',325)

INSERT INTO Products(Description, Price) VALUES('Projector',1500)

INSERT INTO Products(Description, Price) VALUES('XBox',400)

GO

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 500)

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(5, 325)

INSERT INTO Purchases(ProductID, PurchasePrice) VALUES(1, 525)

GO

Listing C:
CREATE VIEW vw_ProductPurchases

AS
      SELECT
            pr.ProductID,
            pr.Description,
            pr.Price AS ProductPrice,
            pu.PurchasePrice,
            pu.PurchaseDate  
      FROM
            Products pr
            INNER JOIN Purchases pu ON pr.ProductID = pu.ProductID

GO
Listing D:
CREATE TRIGGER tr_vwProductPurchases ON vw_ProductPurchases

INSTEAD OF INSERT

AS

BEGIN
      IF EXISTS
      (
            SELECT TOP 1 *
            FROM INSERTED
            WHERE
                  ProductID IS NOT NULL AND
                  ISNULL(COALESCE(PurchasePrice, ProductPrice),0)>0
      )
      BEGIN
            INSERT INTO Purchases
            (
                  ProductID, PurchasePrice, PurchaseDate
            )
            SELECT

i.ProductID, COALESCE(PurchasePrice, ProductPrice), ISNULL(PurchaseDate, GETDATE())
            FROM INSERTED i  
      END
      ELSE
      BEGIN
            PRINT 'Adequate data not provided.'
      END

END