How to use the SQL Server 2005 INSTEAD-OF trigger

A trigger is similar to a stored program. Database ObjectIt responds to a request in the database environment. SQL Sever 2005 contains three trigger objects: AFTER, Data Definition Language (DDL), and INSTEAD-.
The AFTER trigger is a stored program. It occurs AFTER a data operation statement, such as a delete statement. DDL is a new trigger of SQL Server 2005 that allows you to respond to object-defined horizontal events (for example, DROP TABLE statements) in the database engine ). The INSTEAD-OF trigger is an object and can be executed in the database engine instead of data operation statements. For example, if you attach an INSTEAD-of insert trigger to a table, the database is notified to execute this trigger.
Reason for using the INSTEAD-OF trigger
The INSTEAD-OF trigger is a powerful object of SQL Sever, allowing developers to transfer the database engine to complete different WorkTo meet the development requirements. One example is to add an INSTEAD-OF trigger to the database table. If you do not need to modify the table, you can roll back the table content. When using this method, you must be extremely careful because the INSTEAD-OF trigger must be activated before any specified table is modified.
A better reason for using the INSTEAD-OF trigger is view processing. After the INSTEAD-OF trigger is added to the view, you can create an updated view. Updatable views allow the entire extraction of database outlines, so you can use this method to design a system without having to worry about OLTP database outlines and replace the data to modify a set of standard visual charts.
Example
To better describe the concept of updatable views, we provide an example. In this example, we design a product table (record product) and a purchase table (record purchase ). Listing A contains the table creation script. After running this script, you can obtain the table used in the example. Run the Listing B script to add data to the table.
Now that the table has data, I can create some meaningful views for these tables. View Listing C.
This is a typical product level view. It integrates two tables in the database to simplify the data. However, for data extraction, using views has no advantages. After the INSTEAD-OF trigger is attached to the view, the table can be modified, but I do not need to directly modify the data in the table. I use the code in Listing D to create an INSTEAD-OF trigger in the vw_ProductPurchases view.
Note the declaration OF this INSTEAD trigger. The default trigger created by SQL Server is the AFTER trigger. Therefore, you must specify the INSTEAD OF clause in the trigger definition.
The first statement of the trigger is the "check" statement. In this example, I use this statement to check the INSERTED table to ensure that the ProductID field is displayed and that other PurchasePrice or ProductPrice fields are displayed.