触发器

数据库触发器是存储于数据库的命名PL/SQL语句块，当触发事件发生时它们会隐含执行。

触发事件可以是如下任何一种：

1> 处理数据库表的DML语句（如INSERT,UPDATE或者DELETE）。在触发事件发生之前或者之后，触发器会执行。

2> 特定用户在特定模式下，或者任何用户执行的DDL语句（如CREATE或者ALTER）。这种触发器经常被用于审计目的。它们可以记录各种模式修改，何时执行，以及哪个用户执行的。

3> 系统事件，如数据库启动或者关闭

4> 用户事件，如登陆和注销。即可以定义一个触发器，在用户登陆数据库时记录用户名和登陆事件。

创建触发器的通用语法如下所示：

CREATE [OR REPLACE] TRIGGER Trigger_name
{BEFORE |AFTER} Triggering_event ON table_name
[FOR EACH ROW]
[FOLLOWS another_trigger]
[ENABLE/DISABLE]
[WHEN condition]
DECLARE
     declaration statements
BEGIN
     executable statements
EXCEPTION 
     exception-handling statements
END;

其中，trigger_name是触发器的名称。BEFORE或者AFTER指明触发器何时执行，即在触发事件发生之前，还是之后，trigger_event是针对数据库表的DML语句。table_name是与该触发器相关的数据库表的名称。子句FOR EACH ROW指行触发器。FOLLOWS选项，指定触发器被触发的顺序。这个选项适用于在相同表上所定义的，并且会在相同时间点执行的触发器。ENABLE和DISABLE子句指定触发器是在启用，还是禁用状态下被创建的。默认情况下是启用的。

禁用或启用触发器，可用以下命令：ALTER TRIGGER trigger_name DISABLE/ENABLE;

禁用某个表上的所有触发器,可用如下命令：ALTER TABLE table_name DISABLE ALL TRIGGERS.

触发器按照不同的分法，有不同的分类，主要有以下两种：

按触发的时间可分为：BEFORE触发器和AFTER触发器

按触发的类型可分为：行触发器，语句触发器和INSTEAD OF触发器

下面，我们结合具体的实例来演示不同的触发器。

一、 BEFORE触发器

范例一：

create or replace trigger student_i
before insert on student
for each row
BEGIN
  :NEW.student.id := student_seq.nextval;
  :NEW.created_by := USER;
  :NEW.created_date := SYSDATE;
END;

注意：触发器包含伪记录：NEW,使得你可以访问正被插入student表的数据行。为访问伪记录:NEW的单独成员，需要使用点符号:

通过PL/SQL表达式访问序列是Oracle 11g的新特性。在Oracle 11g之前，只能通过以下方式获取：

select student_seq.nextval into v_student_id from dual;

:NEW.student.id := v_student_id

范例二：

create or replace trigger emp_update
before update or delete or insert on emp
for each row
begin
   if updating or deleting or inserting then
      raise_application_error(-20001,'The table emp can not be modified');
   end if;
end;

该触发器的代码部分有三个布尔函数-updating,deleting,inserting,如果对这个表执行update操作，则函数updating的值为TRUE；如果对这个表执行delete操作，则函数deleting的值为TRUE。insert操作亦然。

范例三：

create or replace trigger emp_update
before update of sal on emp
for each row
begin
   if updating then
       raise_application_error(-20001,'Salary can not be modofied');
   end if;
end;

可见，对某列也可使用触发器

二、 AFTER触发器

范例四：

create or replace trigger dept_delete
after delete on dept
for each row
begin
   if deleting then
      delete from emp where deptno = :old.deptno;
   end if;
end;

dept和emp表存在外键约束，即dept表的主键deptno是emp表的外键，当我们删除dept表的行记录时，如果待删除的deptno在emp表中存在对应的记录，则会报ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found。构造触发器如上，当我们删除dept的行记录时，会自动删除deptno在emp表中对应的行记录。

三、自治事务

trigger语句块中不能含有事务处理语句，如commit和rollback。倘若需要需要事务处理语句，则必须用到自治事务。

四、行触发器

行触发器指的是触发器被触发的次数等同于触发语句所影响的数据行数量。当语句FOR EACH ROW出现在CREATE TRIGGER子句中，该触发器就是行触发器。

上述范例均属于行触发器。

五、语句触发器

对于语句触发器而言，每执行一次触发语句，该触发器就会执行一次，也就是说，不管触发语句影响多少数据行，该触发器只会执行一次。

当该触发器所执行的操作不依赖于单独记录中的数据时，就应该使用语句触发器。例如，如果希望限制只能在上班时间访问emp表，就应该使用语句触发器。

范例五：

create or replace trigger emp_record
before insert or update or delete on emp
declare
   v_day varchar2(10);
begin
   v_day := rtrim(to_char(sysdate,'DAY'));
   if v_day in ('SATURDAY','SUNDAY') then 
      raise_application_error(-20000,'The table can not be modified during off hours');
   end if;
end;

EMP表只能在工作日修改。

六、替代触发器

到目前为止，我们所涉及的触发器都是基于表的，其实，Oracle还提供了另一种触发器，这种触发器是在数据库视图上创建的，即替代触发器。替代触发器会代替基于视图的DML操作（INSERT、UPDATE、DELETE）,而直接作用于底层的数据库表。我们来看看下面的实验：

1> 创建视图

SQL> create or replace view v_test as select e.empno,e.ename,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

2> 查询视图

SQL> select * from v_test;

EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
...

3> 插入数据

SQL> insert into v_test values(1234,'VICTOR',50,'IT');

insert into v_test values(1234,'VICTOR',40,'IT')
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

4> 建立替代触发器

CREATE OR REPLACE TRIGGER t_v_test
INSTEAD OF INSERT ON v_test
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT(duplicate_info,-00001);
BEGIN
   INSERT INTO  dept(deptno,dname)
       VALUES(:new.deptno,:new.dname);
   INSERT INTO  emp(empno,ename,deptno)
       VALUES(:new.deptno,:new.ename,:new.deptno);
EXCEPTION
   WHEN duplicate_info THEN 
       RAISE_APPLICATION_ERROR(-20001,'Duplicate empno or deptno');
END;

5> 重新插入数据

SQL> insert into v_test values(1234,'VICTOR',50,'IT');

1 row created.

测试OK！

七、系统触发器

下面试举几例

1> 限制用户从指定IP登陆

CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
   RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
   ALLOWED_IP      VARCHAR2(16) := '192.168.1.1';
   LOGON_USER      VARCHAR2(32);
   CLIENT_IP       VARCHAR2(16);
BEGIN
   LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');         -->> SYS_CONTEXT是一个蛮有用的函数
   CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
   IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
        RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
   END IF;
END;

2> 通过用户级别触发器修改日期格式

CREATE OR REPLACE TRIGGER emp_format
AFTER LOGON ON SCHEMA
DECLARE
   sqlstr VARCHAR2(60) :='alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';   注意：连续两个单引号表示转义
BEGIN
   execute immediate sqlstr;
END;

3> 记录用户的登陆信息

SQL> create table log_table(sid number,serial# number,username varchar2(20),action varchar2(8),log_time varchar2(19));   -->> 首先创建表用于记录用户的登陆信息

CREATE OR REPLACE TRIGGER logon_db
AFTER LOGON ON DATABASE
DECLARE
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
BEGIN
  SELECT sid INTO v_sid FROM v$mystat WHERE rownum=1;
  SELECT serial#,username
  INTO   v_serial#,v_username
  FROM v$session WHERE sid=v_sid;
  INSERT INTO log_table
  VALUES (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
END logon_db;

AFTER LOGON ON DATABASE 也可替换为 BEFORE SHUTDOWN ON DATABASE,BEFORE LOGON ON SCHEMA,ALTER LOGON ON SCHEMA等等。

八、复合触发器

更多相关内容，可参考官方文档：http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS020

posted @ 2014-08-19 17:04 iVictor 阅读(2269) 评论(1) 编辑收藏举报

刷新页面返回顶部

SlowTech

生活本应就是慢的

触发器

公告