Oracle触发器用法实例详解

作者: 云计算机网 分类: 云安全 发布时间: 2017-06-09 18:58

本文实例讲述了Oracle触发器用法。分享给大家供大家参考,具体如下:

一、触发器简介

触发器的定义就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。

因此触发器不需要人为的去调用,也不能调用。

然后,触发器的触发条件其实在你定义的时候就已经设定好了。

这里面需要说明一下,触发器可以分为语句级触发器和行级触发器。

详细的介绍可以参考网上的资料,简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。

具体举例:

1、 在一个表中定义的语句级的触发器,当这个表被删除时,程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器,那当这个表中一行数据发生变化的时候,比如删除了一行记录,那触发器也会被自动执行了。

二、触发器语法

触发器的语法:

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
pl/sql语句
end

其中:

触发器名:触发器对象的名称。由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。
触发时间:指明触发器何时执行,该值可取:
before:表示在数据库动作之前触发器执行;
after:表示在数据库动作之后触发器执行。
触发事件:指明哪些数据库动作会触发此触发器:
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器。
表 名:数据库触发器所在的表。
for each row:对表的每一行触发器执行一次。如果没有这一选项,则只对整个表执行一次。

触发器能实现如下功能:

功能:

1、 允许/限制对表的修改
2、 自动生成派生列,比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑

举例

1)、下面的触发器在更新表tb_emp之前触发,目的是不允许在周末修改表:

create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
IF(to_char(sysdate,'DY')='星期日') THEN
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
END IF;
END;
/

2)、使用触发器实现序号自增

创建一个测试表:

create table tab_user(
id number(11) primary key,
username varchar(50),
password varchar(50)
);

创建一个序列:

create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;
  • 开发中肯定会用到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;

    这里看到在创建触发器以后如果仍然使用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;

    这里建立触发器以后,当你想改变所有人的工资时就会出触发器的错误,所有人的工资即表示会影响很多行。 B、行级触发器:针对需要操作的那一行,有关键词:for each row,用来 (1)实现数据的审计功能: Example:做一个记录删除员工信息的表记录被删除员工的信息 这里为了不改变oracle中emp表的数据,新建一个emp_new表 #p#分页标题#e#

    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;

    这里可以看到在创建触发器时,用到了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.sallt;old.sal OR new.salgt;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_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;

    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=20
    WITH CHECK OPTION;

    这里有个条件部门号为20,则任何修改这个视图的语句都必须针对的是20号部门的员工。 继续替代触发器的概念:关键字insteadof,主要针对一些复杂的视图,因为级联表所产生的视图不可以使用update,insert,delete等关键字,没有before,after等关键字,并且不可以建立在with check option选项的视图上,比如新建一个emp表和dept表的级联视图,则不可以向其中添加数据,现在通过触发器解决: Example: 仍然新建2个表分别和emp表dept表的数据相同。 #p#分页标题#e#

    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;

    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_trigger
    AFTER STARTUP ON DATABASE
    BEGIN
    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_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子查询相关内容(包
  • Linux下Oracle12CR2图形化安
  • CentOS6.10静默安装Oracle1
  • 最全的CentOS6.10详细安装
  • Oracle游标使用详解
  • Oracledecode函数
  • Oracle中print
  • Oracle解决高水位线(hi
  • RHEL7上Oracle莫名宕机ORA
  • 网站内容禁止违规转载,转载授权联系中国云计算网