数据库系统概论—触发器

大家好，欢迎来到IT知识分享网。

一 触发器定义

触发器又叫做事件-条件-动作(event-condition-action)规则。当特定的系统事件(如对一个表的增、删、改操作，事务的结束等)发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段SQL存储过程。

下面对定义触发器的各部分语法进行详细说明。

[例5.21]  当对表SC的Grade属性进行修改时，若分数增加了10%,则将此次操作记录到另一个表SC_U(Sno、Cno、Oldgrade、Newgrade)中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

CREATE TABLE SC (Sno CHAR(9) NOT NULL, Cno CHAR(4) NOT NULL, Grade SMALLINT, PRIMARY KEY(Sno, Cno) ); CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN('男', '女')), Sage SMALLINT, Sdept CHAR(20) ); CREATE TABLE SC_U (Sno CHAR(9) PRIMARY KEY, Cno CHAR(4), OldGrade SMALLINT, NewGrade SMALLINT ); CREATE TRIGGER SC_Tt ON SC INSTEAD OF UPDATE AS BEGIN DECLARE @OldGrade DECIMAL(3,1), @NewGrade DECIMAL(3,1), @Sno CHAR(5), @Cno CHAR(4); DECLARE cur CURSOR FOR SELECT Old.Grade, New.Grade, Old.Sno, Old.Cno FROM inserted AS New JOIN deleted AS Old ON New.Sno = Old.Sno AND New.Cno = Old.Cno; OPEN cur; FETCH NEXT FROM cur INTO @OldGrade, @NewGrade, @Sno, @Cno; WHILE @@FETCH_STATUS = 0 BEGIN IF (@NewGrade >= 1.1 * @OldGrade) BEGIN INSERT INTO SC_U (Sno, Cno, OldGrade, NewGrade) VALUES (@Sno, @Cno, @OldGrade, @NewGrade); END; FETCH NEXT FROM cur INTO @OldGrade, @NewGrade, @Sno, @Cno; END; CLOSE cur; DEALLOCATE cur; END; INSERT INTO SC VALUES('123', '456', 50); UPDATE SC SET Grade = 60 WHERE Sno = '123'; SELECT * FROM SC_U

[例5.22]将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLog中。

CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK (Ssex IN('男', '女')), Sage SMALLINT, Sdept CHAR(20) ); CREATE TABLE StudentInsertLog ( InsertedRows INT ); -- 创建触发器 CREATE TRIGGER Student_InsertCount ON Student AFTER INSERT AS BEGIN DECLARE @InsertedCount INT = 0; SELECT @InsertedCount = COUNT(*) FROM inserted; INSERT INTO StudentInsertLog(InsertedRows) VALUES (@InsertedCount); END; INSERT INTO Student VALUES('ABC', 'DEF', '男', 20, 'DS'); SELECT * FROM StudentInsertLog

[例5.23]定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

（由于版本问题书本的BEFORE行级触发器无法运行成功，所以这里用INSTEAD OF触发器代替）

CREATE TABLE Teacher (Eno NUMERIC(4) PRIMARY KEY, Ename CHAR(10), Job CHAR(8), Sal NUMERIC(7, 2), Deptno NUMERIC(2) ); CREATE TRIGGER Insert_Or_Update_Sall ON Teacher INSTEAD OF INSERT, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Eno NUMERIC(4), @Job CHAR(8), @Sal NUMERIC(7, 2); SELECT @Eno = Eno, @Job = Job, @Sal = Sal FROM inserted; IF @Job = '教授' AND @Sal < 4000 BEGIN SET @Sal = 4000; UPDATE t SET Sal = @Sal FROM Teacher t JOIN inserted i ON t.Eno = i.Eno; END ELSE BEGIN UPDATE t SET Sal = @Sal FROM Teacher t JOIN inserted i ON t.Eno = i.Eno; END END; INSERT INTO Teacher VALUES(1102, 'Franklin', '教授', 100, 1); SELECT * FROM Teacher;