分享SQL SERVER使用嵌套触发器Ⅲ.docVIP

分享SQL SERVER使用嵌套触发器Ⅲ 使用递归触发器解决自引用关系 递归触发器的一种用法是用于带有自引用关系的表（亦称为传递闭包）。例如，表 emp_mgr 定义了： ? 一个公司的雇员 (emp)。 ? ? 每个雇员的经理 (mgr)。 ? ? 组织树中向每个经理汇报的雇员总数 (NoOfReports)。 递归 UPDATE 触发器在插入新雇员记录的情况下可以使 NoOfReports 列保持必威体育精装版。INSERT 触发器更新经理记录的 NoOfReports 列，而该操作递归更新管理层向上其它记录的 NoOfReports 列。 ? USE pubs GO -- Turn recursive triggers ON in the database. ALTER DATABASE pubs ?? SET RECURSIVE_TRIGGERS ON GO CREATE TABLE emp_mgr ( ?? emp char(30) PRIMARY KEY, ??? mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp), ??? NoOfReports int DEFAULT 0 ) GO CREATE TRIGGER emp_mgrins ON emp_mgr FOR INSERT AS DECLARE @e char(30), @m char(30) DECLARE c1 CURSOR FOR ?? SELECT emp_mgr.emp ?? FROM?? emp_mgr, inserted ?? WHERE emp_mgr.emp = inserted.mgr ? OPEN c1 FETCH NEXT FROM c1 INTO @e WHILE @@fetch_status = 0 BEGIN ?? UPDATE emp_mgr ?? SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly ?? WHERE emp_mgr.emp = @e??????????????????????????? -- added employee. ? ?? 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 mgrs ?? 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 mgrs ?? 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 Daves manager