第二个触发器应用实例.sql

本套光盘提供了本书各章实例的所需的部分源程序文件以及数据库文件。读者 需要使用JDK 1.4(必需）版

use pubs
go
execute sp_dboption ' ', 'recursive triggers', true
go



CREATE TABLE empMgr 
(
emp char (30) primary key,
mgr char (30) null foreign key references empMgr.emp ,noofreports int default (0)
)

go

CREATE trigger empMgrins
ON empMgr
FOR insert
As

DECLARE @e char (30), @m char (30)
/*定义游标*/
DECLARE c1 cursor for
SELECT empMgr.emp
FROM empMgr, inserted
WHERE empMgr.emp = inserted.mgr
/*打开游标*/
OPEN c1
/*从游标中读取数据并存入变量@e 中*/
FETCH NEXT FROM c1 into @e
WHILE @@fetch_status = 0
BEGIN
UPDATE empMgr
SET empMgr.noofreports = empMgr.noofreports + 1
WHERE empMgr.emp = @e
FETCH NEXT FROM c1 into @e
END
/*关闭游标*/
CLOSE c1
/*释放游标*/
DEALLOCATE c1
go

CREATE trigger empMgrupd on empMgr
FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE empMgr
SET empMgr.noofreports = empMgr.noofreports + 1 
FROM inserted
WHERE empMgr.emp = inserted.mgr
UPDATE empMgr
SET empMgr.noofreports = empMgr.noofreports - 1
FROM deleted
WHERE empMgr.emp = deleted.mgr
END
GO