Oracle触发器详细讲解

栏目: 数据库 · Oracle · 发布时间: 6年前

内容简介:CREATE OR REPLACE TRIGGER scott_triggerBEFORE DDL

开发中肯定会用到Oracle的触发器,本文进行详细讲解。

这里实例中用到的主要是Oracle中scott用户下的emp以及dept表,数据如下

Oracle触发器详细讲解 Oracle触发器详细讲解

一、触发器概念

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;

Oracle触发器详细讲解

这里看到在创建触发器以后如果仍然使用ddl操作,便会报错。

2、dml触发器:基于dml操作的触发器,细分又可以分为行触发器和语句触发器。

A、语句触发器:dml操作可能会影响很多行,主要用于对数据的安全保护。

Example:禁止在周四,周五修改emp表数据

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;

Oracle触发器详细讲解

这里建立触发器以后,当你想改变所有人的工资时就会出触发器的错误,所有人的工资即表示会影响很多行。

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;

Oracle触发器详细讲解

这里可以看到在创建触发器时,用到了for each row关键词,:old.***用来表示更改以前的表中的数据,:new.***用来表示更改以后的数据,在删除数据以后在日志表就有对应的记录。

(2)实现数据完整性:

Example:要求员工涨工资后,不能低于原来的工资,所涨工资也不能高于原来的50%。

这里为了不改变oracle中emp表的数据,新建一个emp_new表

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';

Oracle触发器详细讲解

这里可以看到当改变数据时会触发触发器错误,对表中某一个字段的修改用UPDATE OF即可,另外如果new和old在PLSQL块的外部

即BEGIN外面不可以加冒号。

(3)参照完整性:

Example:主要用于级联更新,如更新dept表中的deptno时,emp表的deptno也更新。

这里仍然新建2个表分别和emp表dept表的数据相同。

create table emp_new

as

select * from emp;create table dept_new

as

select * from dept;CREATE OR REPLACE TRIGGER cascade_trigger

AFTER UPDATE OF deptno ON dept_new

FOR EACH ROW

BEGIN

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;

Oracle触发器详细讲解

select * from emp_new;

Oracle触发器详细讲解

这里参照完整新指具有主从关系的多个表,当更新主表主键时需要更新从表的相关数据。

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=20

WITH CHECK OPTION;

这里有个条件部门号为20,则任何修改这个视图的语句都必须针对的是20号部门的员工。

继续替代触发器的概念:关键字insteadof,主要针对一些复杂的视图,因为级联表所产生的视图不可以使用update,insert,delete等关键字,没有before,after等关键字,并且不可以建立在with check option选项的视图上,比如新建一个emp表和dept表的级联视图,则不可以向其中添加数据,现在通过触发器解决:

Example:

仍然新建2个表分别和emp表dept表的数据相同。

CREATE TABLE emp_new

AS

SELECT * FROM emp;

CREATE TABLE dept_new

AS

SELECT * FROM dept;CREATE VIEW emp_dept

AS

SELECT d.deptno,d.dname,e.empno,e.ename

FROM dept_new d,emp_new e

WHERE d.deptno=e.deptno;

这里scott用户需要先通过sysdba授权才能建立视图:

grant create view to scott;

CREATE OR REPLACE TRIGGER insteadof_trigger

INSTEAD OF INSERT ON emp_dept

FOR EACH ROW

DECLARE

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;

Oracle触发器详细讲解

select * from dept_new;

Oracle触发器详细讲解

这里触发器中当对视图进行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_trigger

AFTER STARTUP ON DATABASE

BEGIN

INSERT INTO event_table VALUES(ora_sysevent,sysdate);

END;

Oracle触发器详细讲解

select * from event_table;

Oracle触发器详细讲解

三、触发器的综合实例

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_trigger

AFTER CREATE OR DROP OR ALTER ON DATABASE

BEGIN

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;

Oracle触发器详细讲解

发现有数据,说明一个日志表成功做好,监视一些用户操作的触发器就做好了。

至此,触发器全部说明完毕,不足之处还请评论说明,谢谢。

更多Oracle相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

Linux公社的RSS地址: https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-08/153767.htm


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

The Web Application Hacker's Handbook

The Web Application Hacker's Handbook

Dafydd Stuttard、Marcus Pinto / Wiley / 2011-9-27 / USD 50.00

The highly successful security book returns with a new edition, completely updated Web applications are the front door to most organizations, exposing them to attacks that may disclose personal infor......一起来看看 《The Web Application Hacker's Handbook》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具