Using INSTEAD OF Triggers On Views

Using INSTEAD OF triggers you can override an INSERT, UPDATE, or DELETE operation on a view. For example, you might define an INSTEAD OF INSERT trigger on a view to replace the standard INSERT statement.

Assume you start with the following view in the pubs database:

CREATE VIEW AuthorsNames
AS
SELECT au_id, au_fname, au_lname
FROM authors 

You might want to insert data to columns not visible in the view. To do so, create an INSTEAD OF trigger on the view to handle inserts.

CREATE TRIGGER ShowInsert on AuthorsNames
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO authors
   SELECT address, au_fname, au_id, au_lname, city, contract, phone, state, zip
   FROM inserted
END

For more information and for examples of INSTEAD OF triggers, see the documentation for your database server. If you are using Microsoft SQL Server, see "INSTEAD OF" in SQL Server Books Online.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2015 Microsoft