INSTEAD OF INSERT trigger for a table : Trigger « Trigger « SQL Server / T-SQL Tutorial

2> CREATE TABLE MyTable(
3>   ID    int      NOT NULL PRIMARY KEY,
4>   Descr char (5) NOT NULL
5> )
6> GO
1> CREATE TRIGGER tri_MyTable ON MyTable INSTEAD OF INSERT
2> AS
3> IF @@ROWCOUNT = 0
4>   RETURN
5> UPDATE F             -- rows that already exist
6> SET
7>   Descr = I.Descr
8> FROM
9>     inserted   AS I
10>   JOIN
11>     MyTable AS F ON F.ID = I.ID
12> INSERT MyTable    -- new rows
13> SELECT
14>   ID,
15>   Descr
16> FROM
17>     inserted AS I
18> WHERE NOT EXISTS
19> (
20>   SELECT
21>     *
22>   FROM
23>       MyTable AS F
24>   WHERE
25>       F.ID = I.ID
26> )
27> GO
1> INSERT MyTable (ID, Descr) VALUES (1, 'a')
2> INSERT MyTable (ID, Descr) VALUES (2, 'b')
3> INSERT MyTable (ID, Descr) VALUES (3, 'c')
4> INSERT MyTable (ID, Descr) VALUES (1, 'd')
5> INSERT MyTable (ID, Descr) VALUES (1, 'e')
6>
7> drop table MyTable
8> GO

(0 rows affected)

(1 rows affected)

(0 rows affected)

(1 rows affected)

(0 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>

22.1.Trigger
	22.1.1.	The syntax of the CREATE TRIGGER statement
	22.1.2.	exits if the price column has not been updated.
	22.1.3.	Define variables in a trigger
	22.1.4.	Update table in a trigger
	22.1.5.	Check @@ROWCOUNT in a trigger
	22.1.6.	Rollback transaction in a trigger
	22.1.7.	RAISERROR in trigger
	22.1.8.	Disable a trigger
	22.1.9.	Enable a trigger
	22.1.10.	Check business logic in a trigger
	22.1.11.	Check record matching in a trigger
	22.1.12.	Table for INSTEAD OF Trigger for Logical Deletes
	22.1.13.	INSTEAD OF INSERT trigger for a table
	22.1.14.	Trigger Scripts for Cascading DELETEs