How to use SQL Server instead-of triggers

A trigger is a database object similar to a stored program that responds to a request in a database environment. SQL Sever 2005 contains 3 trigger objects: After, data definition language (DDL), and instead-of.

After triggers are stored programs that occur after the action of a data manipulation statement, such as deleting statements. DDL is a new trigger for SQL Server 2005 that allows you to respond to object-defined horizontal events (for example, DROP table statements) in the database engine. instead-of triggers are objects that can be executed in lieu of data manipulation statements in the database engine. For example, attach a instead-of insert trigger to a table and tell the database to execute the trigger.

Reasons to use instead-of triggers

Instead-of triggers are powerful objects for SQL Sever, allowing developers to transfer the database engine to complete different tasks to meet development requirements. One example is to add a instead-of trigger to a table in the database, and you can roll back the contents of the table when you do not need to modify the table. When using this method, you must be extremely careful because the instead-of trigger must be active before any specified table modifications.

A more compelling reason for using instead-of triggers is view processing. After you add a instead-of trigger to a view, you can create an updated view. Updatable views allow you to fully extract the database outline, so you can design your system in this way without worrying about the problem of an OLTP database outline, and instead of modifying a set of standard views for data modification.

Example

<b>Listing A</b>
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())
)

To better illustrate the concept of updatable views, we provide an example. In this case, we design a product table (record product), a purchase form (record purchase). Listing A contains a script to create a table, and then you run the script to get the table you want to use in the example. Run listing B script to add data to the table.

<b>Listing B</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

Now that there is data in the table, I can create some meaningful views for those tables. Please see listing C.

<b>isting C</b>
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