trigger syntax

instead of trigger

create trigger [owner.] trigger_name
	on [owner.]view_name
	instead of {insert, update, delete}
	as SQL_statements

instead of trigger with update clause

create trigger [owner.] trigger_name
	on [owner.]view_name
	instead of {insert, update, delete}
	as 
		[if update (column_name)
			[{and | or} update (column_name)]...]
			SQL_statements
		[if update (column_name)
			[{and | or} update (column_name)]...
			SQL_statements] ...

drop instead of trigger statement

The syntax for dropping the instead of trigger is the same as for the for triggers.

Parameters

trigger_name – is the name of the trigger, which must conform to the rules for identifiers and is unique in the database. To create another trigger of the same name, owned by a different user in the current database, specify the owner’s name.
view_name – is the name of the view on which to create the trigger.
insert, update, delete – statements that can be included in any combination. delete cannot be used with an if update clause.
SQL_statements – specify trigger conditions and actions. Trigger actions take effect when the user action (insert, update, delete) is attempted.
if update – tests whether a specified column is included in the set list of an update statement, or is affected by an insert statement.

Example 23

Example 1 These examples use instead of triggers to update union views.

CREATE TABLE EmployeeWest (
	empid                          int primary key,
	empname                    varchar(30),
	empdob                       datetime,
	region                           char(5)
		constraint region_chk
		   check (region='West'))

CREATE TABLE EmployeeEast (
	empid                          int primary key,
	empname                    varchar(30),
	empdob                       datetime,
	region                           char(5)
		constraint region_chk
		   check (region='East’))

CREATE VIEW Employees as
	SELECT * FROM EmployeeEast
	UNION ALL
	SELECT * FROM EmployeeWest

CREATE TRIGGER EmployeesInsertTrig on Employees
INSTEAD OF INSERT AS
BEGIN
	
	INSERT INTO EmployeeEast SELECT * FROM inserted where
			region = "East"

	INSERT INTO EmployeeWest SELECT * FROM inserted where
			region = "West"
END

--will insert the data into the EmployeeEast table
INSERT INTO Employees
		values (10, 'Jane Doe', '11/11/1967', 'East')

--will insert the data into the EmployeeWest table
INSERT INTO Employees
		values (11, 'John Smith', '01/12/1977', 'West')

--will insert multiple rows into EmployeeEast and 
--EmployeeWest tables. Employee2 table includes employees 
--from both East and West.
INSERT INTO Employees SELECT * from Employee2

Example 2 These examples use instead of triggers to implement encrypted column support,storing data in the database in encrypted form without changing applications..

CREATE TABLE Employee_t (id int PRIMARY KEY, name varchar(20), 
			salary binary (64))
--where the id and name columns are stored unencrypted, salary is
--encrypted and id is a primary key.

CREATE VIEW employee_v as select id, name, decrypt (salary)
FROM employee_t

CREATE TRIGGER EmployeeInsert
ON employee_v
INSTEAD OF INSERT
AS
BEGIN
	INSERT employee_t SELECT id, name, encrypt (salary)
	FROM inserted
END

CREATE TRIGGER employeeUpdate
ON employee_v
INSTEAD OF UPDATE
AS
BEGIN
	DELETE FROM employee_t WHERE id IN (SELECT id FROM
	deleted)
	INSERT employee_t SELECT id, name, encrypt (salary)
	FROM inserted
END

CREATE TRIGGER employeeDelete
ON employee_v
INSTEAD OF DELETE
AS
BEGIN
	DELETE FROM employee_t WHERE id IN (SELECT id FROM
	deleted)
END

Usage

The default value of owner_name is the current user.
If you use an owner_name to qualify a trigger, you must explicitly qualify the view_name the same way.
You cannot use a variable for a trigger_name.
If more than one view of the same name exists in the database, specify the owner’s name.
If multiple trigger actions are specified in SQL_statements, group them with BEGIN and END.

Restrictions

You cannot create instead of triggers on a table, although triggers can reference tables. You can only create instead of triggers on views.
You can define only one instead of trigger for each insert, update, or delete statement on a view. A view can have one trigger defined for multiple operations, or separate triggers for each operation.
Each new trigger created on a view for the same operation overwrites the previous trigger.
A trigger cannot apply to more than one view.
instead of triggers are not allowed on updatable views using the with check option.
The create trigger statement must be the first statement in the batch. All other statements that follow in that batch are treated as part of the definition of the create trigger statement.
Permission to create triggers defaults to the view owner, who cannot transfer it to other users.
Triggers are database objects, and their names must follow the rules for identifiers.
You can create a trigger only in the current database, although a trigger can reference objects outside of the current database.
Enabling and disabling the instead of trigger is not supported.
If a trigger references table names, column names, or view names that are not valid identifiers, you must set quoted_identifier on before the create trigger command, and enclose each such name in double quotation marks. The quoted_identifier option does not need to be on when the trigger fires; bracketed identifiers work as well.
Using the set cursor rows command with client cursors, cursors declared through Open Client calls or embedded SQL, may prevent positioned delete and update from firing an instead of trigger. A positioned update statement is a SQL update statement that contains the where current of <cursorname> clause to update only the row upon which the cursor, <cursorname>, is currently positioned.
Joins are not allowed in searched delete and update statements that would fire an instead of trigger.
positioned delete and update on cursors defined with joins does not fire an instead of trigger.

A positioned delete is a SQL delete statement containing a where current of <cursorname> clause to delete only the row upon which the cursor, <cursorname>, is currently positioned.

A positioned update is a SQL update statement that contains the where current of <cursorname> clause to update only the row upon which the cursor, <cursorname>, is currently positioned.
For positioned delete and update statements that fire an instead of trigger, the instead of trigger must exist when the cursor is declared.

instead of triggers on tables are not currently implemented.

instead of triggers have the same permission requirements as for triggers: to create a view with instead of triggers, permission for insert/update/delete for the view, not the underlying tables, must be granted to the user.