数据库实验九
程序员文章站
2022-11-30 18:19:02
数据库实验九–【问题9.5】创建触发器,当修改Student表中的数据时,在客户端显示“已修改Student表”提示:PRINT ‘已修改Student表的数据。’CREATE TRIGGER WENTI9_5ON StudentFOR UPDATEASPRINT '已修改Student表的数据' UPDATE Student SET Pwd='11111111' WHERE StuNo='00000001'【问题9.6】修改触发器,当确实修改了Student表的数据后返回“已修改Stu...
数据库实验九
–【问题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
上一篇: 阴道也会“掉皮” 女人私处的7个秘密
下一篇: 安卓第六课