欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库实验九

程序员文章站 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