开发中肯定会用到Oracle的触发器,本文进行详细讲解。 这里实例中用到的主要是Oracle中scott用户下的emp以及dept表,数据如下
一、触发器概念 1、概念: 触发器的本质是一个存储过程,顾名思义发生特定事件时Oracle会执行触发器中的代码。 细分它的组成可以分为3个部分:第一部分在什么条件下触发器会执行,即触发器被触发的事件。第二部分在什么时间点执行触发器 即触发器的发生事件例如before,after。第三部分触发器自身所要做的事情,就是触发器被触发以后具体想表达的事件,在begin和end 之间的sql。 二、触发器的分类: 1、ddl触发器:即执行ddl操作后所触发的事件。 常用的ddl操作有:grant(授权),revoke(撤销授权),create(创建),drop(删除),alter(修改),comment(注释),audit(审核),rename(重命名) 在进行具体实例以前先来讲解另一个概念:oracle中的user和schema: user:oracle中的用户,拥有数据库的对象以及对数据库对象增删改查的权限。schema:该用户下所有数据库对象的集合Collection.类似于生活中 房子schema和房子的拥有者user之间的关系,你是一个用户user你可以通过alter session查看别人的房子,但是你是否可以改变房子中的家具,要看这个房子的拥有者是否grant你这个权限,除非你是所有房子的最高权限人dba。 ddl Example:禁止scott用户的所有ddl操作CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20008,'禁止scott用户的所有ddl操作');
END;create sequence myseq;
CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OR DELETE OR INSERT
ON emp
BEGIN
IF to_char(sysdate,'day') IN ('星期四','星期五') THEN
RAISE_APPLICATION_ERROR(-20008,'不允许在周四周五修改emp表');
END IF;
END;
update emp set sal=800;
这里建立触发器以后,当你想改变所有人的工资时就会出触发器的错误,所有人的工资即表示会影响很多行。 B、行级触发器:针对需要操作的那一行,有关键词:for each row,用来 (1)实现数据的审计功能: Example:做一个记录删除员工信息的表记录被删除员工的信息 这里为了不改变oracle中emp表的数据,新建一个emp_new表create table emp_new
as
select * from emp;create table emp_audit(name varchar2(10),delete_time Date);CREATE OR REPLACE TRIGGER delete_trigger
AFTER DELETE ON emp_new
FOR EACH ROW
BEGIN
INSERT INTO emp_audit values(:old.ename,sysdate);
END;delete from emp_new where empno='7499';select * from emp_audit;
create table emp_new
as
select * from emp;
CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OF sal ON emp_new
FOR EACH ROW
WHEN (new.sal<old.sal OR new.sal>1.5*old.sal)
BEGIN
RAISE_APPLICATION_ERROR(-20008,'工资只增不降,且涨幅不可大于50%');
END;
update emp_new set sal = 1.6*sal where empno='7788';
这里可以看到当改变数据时会触发触发器错误,对表中某一个字段的修改用UPDATE OF即可,另外如果new和old在PLSQL块的外部
即BEGIN外面不可以加冒号。 (3)参照完整性: Example:主要用于级联更新,如更新dept表中的deptno时,emp表的deptno也更新。 这里仍然新建2个表分别和emp表dept表的数据相同。