inserted table Demo : Inserted table « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Inserted table

7>
8> CREATE TABLE Employees
9> (
10>   empid     int          NOT NULL,
11>   mgrid     int          NULL,
12>   empname   varchar(25)  NOT NULL,
13>   salary    money        NOT NULL,
14>   lvl       int          NULL,
15>   hierarchy varchar(900) NULL
16> )
17> GO
1>
2> CREATE TRIGGER myTrigger ON Employees FOR INSERT
3> AS
4> DECLARE @numrows AS int
5> SET @numrows = @@ROWCOUNT
6> IF @numrows > 1
7> BEGIN
8>   RAISERROR('Only single row inserts are supported!', 16, 1)
9>   ROLLBACK TRAN
10> END
11> ELSE
12> IF @numrows = 1
13> BEGIN
14>   UPDATE E
15>     SET lvl = CASE
16>                 WHEN E.mgrid IS NULL THEN 0
17>                 ELSE M.lvl + 1
18>               END,
19>         hierarchy = CASE
20>                       WHEN E.mgrid IS NULL THEN '.'
21>                       ELSE M.hierarchy
22>                     END + CAST(E.empid AS varchar(10)) + '.'
23> FROM
24>     Employees AS E
25>   JOIN
26>     inserted AS I ON I.empid = E.empid
27>   LEFT OUTER JOIN
28>     Employees AS M ON E.mgrid = M.empid
29> END
30> GO
1>
2> --Testing the myTrigger Trigger
3>
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(15, 12, 'Sean', $1500.00)
5> GO

(1 rows affected)
1> drop table Employees;
2> GO
1>