开发中肯定会用到 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_triggerBEFORE DDLON SCHEMABEGIN RAISE_APPLICATION_ERROR(-20008,'禁止 scott 用户的所有 ddl 操作');END;create sequence myseq;
这里看到在创建触发器以后如果仍然使用 ddl 操作, 便会报错.
2,dml 触发器: 基于 dml 操作的触发器, 细分又可以分为行触发器和语句触发器.
A, 语句触发器: dml 操作可能会影响很多行, 主要用于对数据的安全保护.
Example: 禁止在周四, 周五修改 emp 表数据
- CREATE OR REPLACE TRIGGER emp_triggerBEFORE UPDATE OR DELETE OR INSERTON empBEGIN 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_newasselect * from emp;create table emp_audit(name varchar2(10),delete_time Date);CREATE OR REPLACE TRIGGER delete_triggerAFTER DELETE ON emp_newFOR EACH ROWBEGIN INSERT INTO emp_audit values(:old.ename,sysdate);END;delete from emp_new where empno='7499';select * from emp_audit;
这里可以看到在创建触发器时, 用到了 for each row 关键词,:old.*** 用来表示更改以前的表中的数据,:new.*** 用来表示更改以后的数据, 在删除数据以后在日志表就有对应的记录.
(2)实现数据完整性:
Example: 要求员工涨工资后, 不能低于原来的工资, 所涨工资也不能高于原来的 50%.
这里为了不改变 oracle 中 emp 表的数据, 新建一个 emp_new 表
- create table emp_newasselect * from emp;
- CREATE OR REPLACE TRIGGER emp_triggerBEFORE UPDATE OF sal ON emp_newFOR EACH ROWWHEN (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 表的数据相同.
create table emp_newasselect * from emp;create table dept_newasselect * from dept;CREATE OR REPLACE TRIGGER cascade_triggerAFTER UPDATE OF deptno ON dept_newFOR EACH ROWBEGIN UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;END;update dept_new set deptno=15 where deptno=20;select * from dept_new;
select * from emp_new;
这里参照完整新指具有主从关系的多个表, 当更新主表主键时需要更新从表的相关数据.
3, 替代触发器:
这里先讲另一个概念: 带有 with check option 的视图:
如果视图的定义包括条件 (如 where 子句) 并且任何应用于该视图的 INSERT 或 UPDATE 语句都应包括该条件, 则必须使用 WITH CHECK OPTION 定义该视图.
- Example:
- CREATE VIEW emp_view(ename,empno)AS SELECT ename,empno FROM emp WHERE deptno=20WITH CHECK OPTION;
这里有个条件部门号为 20, 则任何修改这个视图的语句都必须针对的是 20 号部门的员工.
继续替代触发器的概念: 关键字 insteadof, 主要针对一些复杂的视图, 因为级联表所产生的视图不可以使用 update,insert,delete 等关键字, 没有 before,after 等关键字, 并且不可以建立在 with check option 选项的视图上, 比如新建一个 emp 表和 dept 表的级联视图, 则不可以向其中添加数据, 现在通过触发器解决:
Example:
仍然新建 2 个表分别和 emp 表 dept 表的数据相同.
CREATE TABLE emp_newASSELECT * FROM emp;CREATE TABLE dept_newASSELECT * FROM dept;CREATE VIEW emp_deptASSELECT d.deptno,d.dname,e.empno,e.enameFROM dept_new d,emp_new eWHERE d.deptno=e.deptno;
这里 scott 用户需要先通过 sysdba 授权才能建立视图:
- grant create view to scott;
- CREATE OR REPLACE TRIGGER insteadof_triggerINSTEAD OF INSERT ON emp_deptFOR EACH ROWDECLARE v_temp INT;BEGIN SELECT COUNT(*) INTO v_temp FROM dept_new WHERE deptno=:new.deptno; IF v_temp=0 THEN INSERT INTO dept_new(deptno,dname) VALUES(:new.deptno,:new.dname); END IF; SELECT COUNT(*) INTO v_temp FROM emp_new WHERE empno=:new.empno; IF v_temp=0 THEN INSERT INTO emp_new(deptno,empno,ename) VALUES(:new.deptno,:new.empno,:new.ename); END IF;END;
- INSERT INTO emp_dept values(15,'HUMANRESOURCE',7999,'LEAF');select * from emp_new;
select * from dept_new;
这里触发器中当对视图进行 insert 时, 会对相应的 emp_new 和 dept_new 进行修改, 也就做到了对复杂视图的修改.
4, 系统触发器:
顾名思义, 由系统触发器所触发的事件, 常用的系统事件 startup,shutdown,db_roll_change,server error 等.
Example: 记录启动数据库时的事件以及时间.
此处因为是系统触发器, 所以需要用 sysdba 的权限登陆.
CREATE TABLE event_table(event VARCHAR2(50),event_time DATE);CREATE OR REPLACE TRIGGER event_triggerAFTER STARTUP ON DATABASEBEGIN INSERT INTO event_table VALUES(ora_sysevent,sysdate);END;
select * from event_table;
三, 触发器的综合实例
Example: 做一个日志用来记录 scott 用户的一些操作:
首先在 sysdba 权限下建立日志表, 序列, 触发器:
CREATE TABLE object_log(logid NUMBER CONSTRAINT pk_logid PRIMARY KEY,operatedate DATE NOT NULL,objecttype VARCHAR2(50) NOT NULL,objectowner VARCHAR2(50) NOT NULL);CREATE SEQUENCE obj_log_seq;CREATE OR REPLACE TRIGGER object_triggerAFTER CREATE OR DROP OR ALTER ON DATABASEBEGIN INSERT INTO object_log VALUES(obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner);END;
在 scott 用户下随便创建个东西:
CREATE SEQUENCE my_seq;
回到 sysdba 权限下查看日志表中是否有对应的记录:
SELECT * FROM object_log;
发现有数据, 说明一个日志表成功做好, 监视一些用户操作的触发器就做好了.
至此, 触发器全部说明完毕, 不足之处还请评论说明, 谢谢.
来源: http://www.linuxidc.com/Linux/2018-08/153767.htm