数据库实验九

–【问题9．5】创建触发器，当修改Student表中的数据时，在客户端显示“已修改Student表”
提示：PRINT ‘已修改Student表的数据。’

CREATE TRIGGER WENTI9_5
ON Student
FOR UPDATE
AS
PRINT '已修改Student表的数据'
 UPDATE Student SET Pwd='11111111' WHERE StuNo='00000001'

【问题9．6】修改触发器，当确实修改了Student表的数据后返回“已修改Student表”，否则返回“不存在要修改的数据”
提示：IF((SELECT COUNT() FROM inserted)<>0)
IF((SELECT COUNT() FROM inserted)<>0)

CREATE TRIGGER WENTI9_6
ON Student
FOR UPDATE
AS
PRINT '已修改Student表的数据'
IF((SELECT COUNT(*) FROM inserted)<>0)
PRINT '不存在要修改的数据' 	
UPDATE Student SET Pwd='22222222' WHERE StuNo='00000001'

【问题9．7】创建触发器，当插入、更新、删除StuCou表的选课数据行时，同时更新Course表中相应的报名人数（单行操作）并显示已更新通知。
提示：
UPDATE Course SET WillNum=WillNum+1 WHERE CouNo=(SELECT CouNo FROM INSERTED)
DELETED参照编写

CREATE TRIGGER WENTI9_7
ON StuCou
FOR INSERT,UPDATE,DELETE
AS
UPDATE Course SET WillNum=WillNum+1 WHERE CouNo=(SELECT CouNo FROM INSERTED)
UPDATE Course SET WillNum=WillNum-1 WHERE CouNo=(SELECT CouNo FROM DELETED)
PRINT '已修改CouNO表的数据'

测试：

SELECT * FROM Course WHERE CouNo='002'
 
SELECT * FROM Course WHERE CouNo='003'
 
UPDATE StuCou SET CouNo='002' WHERE StuNo='00000011' AND CouNo='003'
 
SELECT * FROM Course WHERE CouNo='002'

–【问题9．8】查看数据库中所有触发器

SELECT NAME FROM SYSOBJECTS
WHERE XTYPE='TR'

本文地址：https://blog.csdn.net/weixin_44252029/article/details/107155280