触发器简述

触发器概述

? 为什么要用触发器

– 实现企业自定义的业务规则，它比约束更为复杂和强大。

? 概念

– 数据库特定事件发生时所自动执行的块。



? 用途

– 当表数据发生改变时，校验表(主要用途)。

– 根据数据库发生的事件来完成数据库维护工作。



? 事件分类(根据监控对象来分类)

– DML触发器：监控表

– Instead of触发器：监控视图

– 数据库事件：监控数据库启动、登录、关闭等事件





DML触发器

? 概念

– 当表发生数据修改事件(增加、删除、修改)自动执行的PL/SQL 块。

? 语法

CREATE OR REPLACE TRIGGER 触发器名称
{AFTER|BEFORE } --指定触发时机
{INSERT|DELETE|UPDATE} --指定触发器事件
ON 表名 --指定所监控的表
{FOR EACH ROW|FOR EACH STATEMENT} --指定触发器次数
BEGIN
代码;
END;





{AFTER|BEFORE } --指定触发时机
{INSERT|DELETE|UPDATE} --指定触发器事件
ON 表名 --指定所监控的表
{FOR EACH ROW|FOR EACH STATEMENT} --指定触发器次数

? 相关概念

– AFTER|BEFORE

? 在什么事件之前或之后执行

– INSERT|DELETE|UPDATE

? 什么事件

– ON 表名

? 触发器建在什么表上，即监控什么表

– FOR EACH ROW

? 行级触发

? 示例：delete from t1,删除1000行，则执行1000次(一行一次)

– FOR EACH STATEMENT

? 语句级触发

? 示例： delete from t1,删除1000行，则执行1次(一句一次)

– :new 行变量

? 保存事件发生时新数据所在行。

? 只有insert和update才有新数据。

– :old 行变量

? 保存事件发生时旧数据所在行。

? 只有delete和update才有旧数据

? 案例1：

– 实现表t1(t_id,t_name)的自动编号。

– 分析：当表t1发生Insert事件之前自动执行。添加几行就执行几次。

– 知识点：理解触发器，使用:new关键字

CREATE OR REPLACE TRIGGER tr_t1_autoid
BEFORE INSERT
ON t1
FOR EACH ROW

DECLARE
BEGIN
SELECT seq_t1_id.NEXTVAL INTO :NEW.t_id FROM dual;
END





--过程：建表、建序列、建触发器
CREATE TABLE t1(
t_id NUMBER PRIMARY KEY,
t_name VARCHAR2(10)
);
DROP SEQUENCE seq_t1_id;
CREATE SEQUENCE seq_t1_id;

INSERT INTO t1(t_id,t_name) VALUES(seq_t1_id.nextval,'a');
INSERT INTO t1(t_id,t_name) VALUES(seq_t1_id.nextval,'b');
INSERT INTO t1(t_id,t_name) VALUES(seq_t1_id.nextval,'c');
COMMIT;

SELECT * FROM t1;


INSERT INTO t1(t_name) VALUES('dd');
INSERT INTO t1(t_name) VALUES('ee');
INSERT INTO t1(t_name) VALUES('ff');
COMMIT;

SELECT seq_t1_id.NEXTVAL FROM dual;



--实现表t1(t_id,t_name)的自动

编号。
CREATE OR REPLACE TRIGGER tr_t1_autoid
BEFORE INSERT
ON t1
FOR EACH ROW

DECLARE
BEGIN
SELECT seq_t1_id.NEXTVAL INTO :NEW.t_id FROM dual;
END;





? 案例2：

– 更新员工时，工资只能增加，不能减少。

– 分析：当emp发生update事件时，检查前后工资，如果变少，则通过抛出异常中断更新事件。

– 知识点：使用:old和:new。

CREATE OR REPLACE TRIGGER tr_emp_update_sal
BEFORE UPDATE
ON emp
FOR EACH ROW
DECLARE
BEGIN
IF(:OLD.sal>:NEW.sal)THEN
raise_application_error(-20009,'老板，工资只能加不能减！');
END IF;
END;





CREATE OR REPLACE TRIGGER tr_emp_update_sal
BEFORE UPDATE OR DELETE
ON emp
FOR EACH ROW
DECLARE
BEGIN
IF updating THEN
IF(:OLD.sal>:NEW.sal)THEN
raise_application_error(-20009,'老板，工资只能加不能减！');
END IF;
END IF;
IF deleting THEN
IF(:OLD.job='MANAGER')THEN
raise_application_error(-20009,'不能删除项目经理！');
END IF;
END IF;
END;





? 实例

– 案例3：添加员工信息，如果该部门员工员工人数超过3人，则不能再添加。

? 分析：当emp发生insert事件，检查新员工所在部门人数，如果超过3人，则通过抛出异常中断事务。

? 知识点：使用条件谓词

--添加员工信息，如果该部门员工员工人数超过3人，则不能再添加。
CREATE OR REPLACE TRIGGER tr_emp_add
BEFORE INSERT
ON emp
FOR EACH ROW
DECLARE
v_emp_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_emp_count
FROM emp WHERE deptno = :NEW.deptno;
IF(v_emp_count>3)THEN
raise_application_error(-20010,'此部门人太多，不能添加！');
END IF;
END;





案例4：部门表的部门编号(deptno)更改时，该部门的员工的部门编号也自动修改

CREATE or replace TRIGGER dept_update_cascade

AFTER UPDATE OF deptno ON dept

FOR EACH ROW

BEGIN

UPDATE emp

SET deptno = :new.deptno

WHERE deptno = :old.deptno;

END;

调用：

1、没有直接的调用方式

2、只要dept表的deptno有修改，则“自动”执行此PL/SQL块。

3、触发器更细节的知识将在后面章节专门学习。







? 使用要点

– 只要在数据修改时需要自动完成一些工作，就可以使用触发器。

– 在大型系统中，更要慎重使用触发器。太多触发器很容易造成程序难以维护，甚至造成死循环。

? 使用场合

– 控制数据安全

– 实现数据审计

– 实现数据完整性

– 实现参照完整性

? INSTEAD OF触发器

– 让不能更新的视图(如多表联接)能够完成“更新”



? 系统事件触发器

– 在系统事件(启动，关闭)时自动执行的触发器

– 示例：实例启

动与关闭

– 示例：系统登录及退出



? DDL触发器

– 在创建和删除数据库对象执行的触发器

– 示例：记录scott模式下所有创建和删除对象信息



触发器VS存储过程

　
触发器
存储过程

编写
无参数，返回值
有参数，有返回值

编写
不能commit或rollback
能

调用方式
事件发生时自动调用
手工调用

操作对象
基于某张表
与表无关

4.下面的例子会制作 "Persons" 表的备份复件：

SELECT *
INTO Persons_backup
FROM Persons
IN 子句可用于向另一个数据库中拷贝表：

SELECT *
INTO Persons IN 'Backup.mdb'
FROM Persons
如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons