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>
|