数据库技术六（触发器）

触发器

基本内容：（1）编写后触发器；（2）编写替代触发器 ；（3）删除触发器。

1. (为后续准备基础表和数据)使用SELECT INTO创建学生表_备份和选课表_备份，只备份表结构，用于今后备份学生表和选课表数据。删除选课表上的参照关系。

--备份表结构
SELECT * INTO 学生表_备份 FROM 学生表 WHERE 1=2
SELECT * INTO 选课表_备份 FROM  选课表 WHERE 1=2

2. 创建替代触发器TR1：当修改选课表信息时（一次只修改一行数据），只允许修改成绩，如果修改其他列，提示“不允许修改”（测试要求：分别测试修改选课号，修改成绩，同时修改选课号和成绩三种情况）

--执行UPDATE则会TR1
CREATE TRIGGER TR1 ON dbo.选课表
INSTEAD OF UPDATE
AS
BEGIN
	IF(COLUMNS_UPDATED()=4)
	BEGIN
		UPDATE 选课表 SET 成绩=(SELECT 成绩 FROM inserted)
		WHERE 学号=(SELECT 学号 FROM inserted)
		AND 开课号=(SELECT 开课号 FROM inserted)
	END
	ELSE
	PRINT '不允许修改！'
END
GO

--修改开课号
UPDATE 选课表 SET 开课号='C671' WHERE 开课号='010101'
--修改成绩
UPDATE 选课表 SET 成绩=89 WHERE 学号='S10101' AND 开课号='010101'
--修改成绩和开课号
UPDATE 选课表 SET 成绩=80 ,开课号='C666' WHERE 学号='S010101' AND 开课号='010101'
--查看不同的输出提示

3. 创建UPDATE触发器TR2:每当更新选课表成绩数据时（每次只更新一行数据），自动更新学生表的累计学分（如果更新前的成绩为NULL值或小于60，更新后的值大于60，则累计学分增加相应课程的学分；如果更新前的成绩大于等于60，更新后小于60或为空，则累计学分扣除相应课程的学分）。 （测试要求：测试4种情况（前大于60后大于60；前大于60后小于60；前小于60后小于60；前小于60后大于60）学生表累计学生的变化）

CREATE TRIGGER TR2 ON 选课表
FOR UPDATE
AS
BEGIN
DECLARE @XUEHAO VARCHAR(20),@OLD INT,@NEW INT,@KAIKEHAO VARCHAR(20),@XUEFEN VARCHAR(20)
SELECT @XUEHAO=学号，@NEW=成绩，@KAIKEHAO=开课号 FROM inserted
SELECT @OLD=成绩 FROM deleted
SELECT @XUEFEN = 学分 FROM 课程表
WHERE 课号=(SELECT 课号 FROM 开课表 WHERE 开课号=@KAIKEHAO)
IF((@OLD IS NULL OR @OLD<60) AND @NEW>=60)
UPDATE 学生表 SET 累计学分=累计学分+@XUEFEN
ELSE IF(@OLD>60 AND (@NEW IS NULL OR @NEW <60))
UPDATE 学生表 SET 累计学费=累计学费-@XUEFEN
END
GO

--分别测试4种情况
--前大于60后大于60
UPDATE 选课表 SET 成绩=99 WHERE 学号='S010101' AND 开课号='010101'
--结果：累计学分不发生修改
--前大于60后小于60
UPDATE 选课表 SET 成绩=59 WHERE 学号='S060101' AND 开课号='010101'
--结果：累计学分减少
--前小于60后小于60
UPDATE 选课表 SET 成绩=58 WHERE 学号='S060101' AND 开课号='010101'
--结果：累计学分不变
--前小于60后大于60
UPDATE 选课表 SET 成绩=90 WHERE 学号='S060101' AND 开课号='010101'
--结果：累计学分增加

4. 创建DELETE触发器TR3:每当删除学生表学生时（一次可能删除多个学生），则同时级联删除选课表中与被删除学生相关的选课记录，并将学生表和选课表中被删除数据分别备份到学生表_备份和选课表_备份。（测试要求：分别测试删除选课学生和删除未选课学生）

下图测试删除选课学生的信息，同时级联删除选课表中的数据，
结果：选课表中的数据被全部删除，选课表_备份中，存放之前选课表中全部数据。

CREATE TRIGGER TR3 ON 学生表
FOR DELETE
AS
BEGIN
--先备份
INSERT 学生表_备份 SELECT * FROM deleted
INSERT 选课表_备份 SELECT * FROM 选课表 WHERE 学号 IN (SELECT 学号 FROM deleted)
--再删除
DELETE FROM 选课表 WHERE 学号 IN(SELECT 学号 FROM deleted)
END
GO

--删除选课学生
DELETE FROM 学生表 WHERE 学号 IN (SELECT 学号 FROM 选课表)
--删除未选课学生
DELETE FROM 学生表 WHERE 学号 NOT IN (SELECT 学号 FROM 选课表)

5. 删除触发器

DROP TRIGGER TR1,TR2,TR3

总结

• 注意一下inserted表和deleted表的使用，容易出错。
• 触发器是一种特殊的存储过程，触发器是通过事件来触发执行的，可用于强化约束，用来维护数据的完整性和一致性。

待完善问题：after\\before等详细细节