Share via

Using INSTEAD OF Triggers On Views

  • Article

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.

See Also

Other Resources

Working with Views

Working with Triggers