sql 语句 查询

需要数据库文件的联系QQ:157372883

 【例11．1】
（1）
USE Xk
GO
CREATE TRIGGER Test1
ON Student
FOR UPDATE
AS
PRINT '记录已修改！'
GO
（2）
UPDATE Student SET Pwd='11111111' WHERE StuNo='00000001'

【例11．3】
（1）
USE Xk
GO
ALTER TRIGGER Test1
ON Student
INSTEAD OF UPDATE
AS
PRINT '记录没有修改！'
GO
（2）
UPDATE Student SET Pwd='22222222' WHERE StuNo='00000001'

【例11．4】
（1）
USE Xk
GO
CREATE TRIGGER SetWillNum 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)
（2）
SELECT * FROM Course WHERE CouNo='002'
SELECT * FROM Course WHERE CouNo='003'
（3）
UPDATE StuCou SET CouNo='002' WHERE StuNo='00000011' AND CouNo='003'
（4）
SELECT * FROM Course WHERE CouNo='002'
SELECT * FROM Course WHERE CouNo='003'
（5）
USE Xk
GO
ALTER TRIGGER SetWillNum ON StuCou
FOR INSERT, UPDATE, DELETE
AS
UPDATE Course SET WillNum=(SELECT COUNT(*) FROM StuCou WHERE CouNo=Course.CouNo)

【例11．5】
USE Xk
GO
ALTER TRIGGER Test1
ON Student
FOR UPDATE
AS
IF UPDATE(Pwd) PRINT '密码已修改！'
GO

【例11．6】
USE Xk
GO
CREATE TRIGGER Test2
ON Student
FOR UPDATE
AS
PRINT '二次触发！'
GO
测试：
UPDATE Student SET Pwd='22222222' WHERE StuNo='00000001'

【例11．7】
USE Xk
DROP TRIGGER Test1

【例11．9】
USE Xk
GO
sp_rename SetWillNum,UpdateWillNum

【例11．10】
USE Xk
GO
ALTER TABLE StuCou DISABLE TRIGGER UpdateWillNum

【例11．11】
USE Xk
GO
ALTER TABLE StuCou ENABLE TRIGGER UpdateWillNum

【例11．12】
USE Xk
GO
SELECT * FROM sysobjects WHERE TYPE='TR'