一 触发器定义
触发器又叫做事件-条件-动作(event-condition-action)规则。当特定的系统事件(如对一个表的增、删、改操作,事务的结束等)发生时,对规则的条件进行检查,如果条件成立则执行规则中的动作,否则不执行该动作。规则中的动作体可以很复杂,可以涉及其他表和其他数据库对象,通常是一段SQL存储过程。
下面对定义触发器的各部分语法进行详细说明。
(1)只有表的拥有者,即创建表的用户才可以在表上创建触发器,并且一个表上只能创建一定数量的触发器。触发器的具体数量由具体的关系数据库管理系统在设计时确定。
(2)触发器名
触发器名可以包含模式名,也可以不包含模式名。同一模式下,触发器名必须是唯的,并且触发器名和表名必须在同一模式下。
(3)表名
触发器只能定义在基本表上,不能定义在视图上。当基本表的数据发生变化时,将激活定义在该表上相应触发事件的触发器,因此该表也称为触发器的目标表。
(4)触发事件
触发事件可以是INSERT、DELETE或UPDATE,也可以是这几个事件的组合,如INSERT OR DELETE等,还可以是UPDATE OF<触发列,…>,即进一步指明修改哪些列时激活触发器。AFTER/BEFORE是触发的时机。AFTER表示在触发事件的操作执行之后激活触发器;BEFORE表示在触发事件的操作执行之前激活触发器。
(5)触发器类型
触发器按照所触发动作的间隔尺寸可以分为行级触发器(FOR EACH ROW)和语句级触发器(FOR EACH STATEMENT)。
(6)触发条件
触发器被激活时,只有当触发条件为真时触发动作体才执行,否则触发动作体不执行。如果省略WHEN触发条件,则触发动作体在触发器激活后立即执行。
(7)触发动作体
触发动作体既可以是一个匿名PL/SQL过程块,也可以是对已创建存储过程的调用。如果是行级触发器,用户可以在过程体中使用NEW和OLD引用UPDATE/INSERT事件之后的新值和UPDATE/DELETE事件之前的旧值;如果是语句级触发器,则不能在触发动作体中使用NEW或OLD进行引用。
如果触发动作体执行失败,激活触发器的事件(即对数据库的增、删、改操作)就会终止执行,触发器的目标表或触发器可能影响的其他对象不发生任何变化。
[例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;