Mega Code Archive
INSTEAD OF INSERT trigger for a table
<![CDATA[
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>
]]>