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