数据库系统概论—触发器

一 触发器定义

触发器又叫做事件-条件-动作(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;