|
FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
-- 1. Only singleton updates on emp_mgr.
-- 2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports 1 -- Increment mgr's
FROM inserted -- (no. of reports) by
WHERE emp_mgr.emp = inserted.mgr -- 1 for the new report.
UPDATE emp_mgr
SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
FROM deleted -- (no. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave'
GO
SELECT * FROM emp_mgr
GO
以下是更新前的结果：
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Joe 0
Harry NULL 1
Joe Alice 1
Paul Alice 0
以下为更新后的结果：
emp mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Harry 0
Harry NULL 2
Joe Alice 0
Paul Alice 0
|
| 共2页: 上一页 [1] 2 下一页 |
评论加载中…
