SQL_Oracle触发器详细介绍

Oracle触发器详细介绍一
触发器
　　是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。
　　功能：
　　1、 允许/限制对表的修改
　　2、 自动生成派生列，比如自增字段
　　3、 强制数据一致性
　　4、 提供审计和日志记录
　　5、 防止无效的事务处理
　　6、 启用复杂的业务逻辑
　　开始
　　create trigger biufer_employees_department_id
　　       before insert or update
　　              of department_id
　　              on employees
　　       referencing old as old_value
　　                       new as new_value
　　       for each row
　　       when (new_value.department_id<>80 )
　　begin
　　       :new_value.commission_pct :=0;
　　end;
　　/
　　触发器的组成部分：
　　1、 触发器名称
　　2、 触发语句
　　3、 触发器限制
　　4、 触发操作
　　1、 触发器名称
　　create trigger biufer_employees_department_id
　　命名习惯：
　　biufer（before insert update for each row）
　　employees 表名
　　department_id 列名
　　2、 触发语句
　　比如：
　　表或视图上的DML语句
　　DDL语句
　　数据库关闭或启动,startup shutdown 等等
　　before insert or update
　　              of department_id
　　              on employees
　　       referencing old as old_value
　　                       new as new_value
　　       for each row
　　说明：
　　1、 无论是否规定了department_id ，对employees表进行insert的时候
　　2、 对employees表的department_id列进行update的时候
　　3、 触发器限制
　　when (new_value.department_id<>80 )
　　限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。
　　其中的new_value是代表更新之后的值。
　　4、 触发操作
　　是触发器的主体
　　begin
　　       :new_value.commission_pct :=0;
　　end;
　　主体很简单，就是将更新后的commission_pct列置为0
　　触发：
　　insert into employees(employee_id,
　　last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
　　values( 12345,’Chen’,’Donny’, sysdate, 12, ‘[e-mail]’,60,10000,.25);
　　select commission_pct from employees where employee_id=12345;
　　触发器不会通知用户，便改变了用户的输入值。
　　触发器类型：
　　1、 语句触发器
　　2、 行触发器
　　3、 INSTEAD OF 触发器
　　4、 系统条件触发器
　　5、 用户事件触发器

注释：
before和after：指在事件发生之前或之后激活触发器。
instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。
table_or_view_name：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。
when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。
declare---end：是一个标准的PL/SQL块。
 
Oracle触发器详细介绍二--语句触发器
1、 语句触发器
　　是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。
　　例子：
　　需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。
　　Create table foo(a number);
　　Create trigger biud_foo
　　       Before insert or update or delete
　　              On foo
　　Begin
　　       If user not in (‘DONNY’) then
　　              Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
　　       End if;
　　End;
　　/
　　即使SYS，SYSTEM用户也不能修改foo表
　　[试验]
　　对修改表的时间、人物进行日志记录。
　　1、 建立试验表
　　create table employees_copy as select *from hr.employees
　　2、 建立日志表
　　create table employees_log(
　　        who varchar2(30),
　　        when date);
　　3、 在employees_copy表上建立语句触发器，在触发器中填充employees_log 表。
　　Create or replace trigger biud_employee_copy
　　              Before insert or update or delete
　　                     On employees_copy
　　       Begin
　　              Insert into employees_log(
　　                     Who,when)
　　              Values( user, sysdate);
　　             
　　       End;
　　       /
　　4、 测试
　　update employees_copy set salary= salary*1.1;
　　
　　
　　select *from employess_log;
　　
　　
　　5、 确定是哪个语句起作用？
　　即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？
　　可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词，作判断：
　　begin
　　        if inserting then
　　               -----
　　        elsif updating then
　　               -----
　　        elsif deleting then
　　               ------
　　        end if;
　　end; 
　　if updating(‘COL1’) or updating(‘COL2’) then
　　        ------
　　end if;
　　[试验]
　　1、 修改日志表
　　alter table employees_log
　　        add (action varchar2(20));
　　2、 修改触发器，以便记录语句类型。
　　Create or replace trigger biud_employee_copy
　　              Before insert or update or delete
　　                     On employees_copy
　　       Declare
　　              L_action employees_log.action%type;
　　       Begin
　　        if inserting then
　　               l_action:=’Insert’;
　　        elsif updating then
　　               l_action:=’Update’;
　　        elsif deleting then
　　               l_action:=’Delete’;
　　        else
　　               raise_application_error(-20001,’You should never ever get this error.’);
　　
　　
　　              Insert into employees_log(
　　                     Who,action,when)
　　              Values( user, l_action,sysdate);
　　       End;
　　       /
　　3、 测试
　　insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
　　       values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
　　
　　
　　select *from employees_log

总结：语句级触发器.(语句级触发器对每个DML语句执行一次）是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。
实例:
create or replace trigger tri_test
after insert or update or delete on test
begin
if updating then
dbms_output.put_line('修改');
elsif deleting then
dbms_output.put_line('删除');
elsif inserting then
dbms_output.put_line('插入');
end if;
end;

 

Oracle触发器详细介绍三--行级触发器

行级触发器
本章介绍行级触发器机制。大部分例子以INSERT出发器给出，行级触发器可从insert update delete语句触发。
1、介绍
触发器是存储在数据库已编译的存储过程，使用的语言是PL/SQL，用编写存储过程一样的方式编写和编译触发器。下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS_OUTPUT在每插入一行数据时打印“executing temp_air”
SQL> set feedback off
SQL> CREATE TABLE temp (N NUMBER);
SQL> CREATE OR REPLACE TRIGGER temp_air
  2  AFTER INSERT ON TEMP
  3  FOR EACH ROW
  4  BEGIN
  5  dbms_output.put_line('executing temp_air');
  6  END;
7   /
8   SQL> INSERT INTO temp VALUES (1);     -- insert 1 row
executing temp_air
SQL> INSERT INTO temp SELECT * FROM temp; -- insert 1 row
executing temp_air
SQL> INSERT INTO temp SELECT * FROM temp; -- inserts 2 rows
executing temp_air
executing temp_air
SQL>
尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。
2、行级触发器语法：
   CREATE OR REPLACE TRIGGER trigger_name
AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
[WHEN (Boolean expression)]
DECLARE
     Local declarations
BEGIN
     Trigger Body written PL/SQL
END;
  ·Trigger_name
      用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息，涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。
        ORA-01476: divisor is equal to zero
  ORA-06512: at "SCOTT.STUDENTS_AIR", line 5
  ORA-04088: error during execution of trigger
    'SCOTT.STUDENTS_AIR'
      行记数从关键字DECLARE行开始，如果没有DECLARE部分，BEGIN语句是第一行。触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。触发器名一般由表名、触发器类型、触发事件，语法如下：
      trigger_name = table_name_[A|B] [I|U|D] [R|S]
     
      trigger_name 最长30个字符，所以有时不得不使用表名缩写。常表名一般要有一个规则的缩写。这样可以减少故障分析处理时间。
      [A|B]        表示是AFTER 或 BEFORE 触发器类型
      [I|U|D]      表示触发事件，可能是 insert ,update 或者delete
      [R|S]        表示行级(row)或语句级(statement)触发器类型。
    ·BEFORE|AFTER insert on table_name
      这条语句告诉Oracle什么时候执行触发器.它可能在ORACLE 完整性约束检查前或后执行，可以指定一个Before或after触发器在多语句操作类型上触发，如：
        BEFORE INSERT OR UPDATE on table_name
  BEFORE INSERT OR UPDATE OR DELETE on table_name
  AFTER INSERT OR DELETE on table_name
      DBMS_STANDARD 包提供了四个boolean函数来区分SQL语句类型。
        PACKAGE DBMS_STANDARD IS
      FUNCTION inserting RETURN BOOLEAN;
      FUNCTION updating RETURN BOOLEAN;
      FUNCTION updating (colnam VARCHAR2) RETURN BOOLEAN;
      FUNCTION deleting RETURN BOOLEAN;
      etc,
  END DBMS_STANDARD;
   在触发器中可以直接使用函数名称，不需要指定包名：
   CREATE OR REPLACE TRIGGER temp_aiur
  AFTER INSERT OR UPDATE ON TEMP
  FOR EACH ROW
  BEGIN
      CASE
      WHEN inserting THEN
          dbms_output.put_line
              ('executing temp_aiur - insert');
      WHEN updating THEN
          dbms_output.put_line
              ('executing temp_aiur - update');
      END CASE;
  END;
      对于Update行级触发器，可以指定被更新的列作为触发器触发条件。
     CREATE OR REPLACE TRIGGER temp_aur
   AFTER INSERT OR UPDATE OF M, P ON TEMP
   FOR EACH ROW
   BEGIN
       dbms_output.put_line
          ('after insert or update of m, p');
   END;
    ·WHEN(BOOLEAN EXPRESSION)
        这是个可选语句，用来过滤触发触发器的条件。
       
        CREATE OR REPLACE TRIGGER temp_air
   AFTER INSERT ON TEMP
   FOR EACH ROW
   WHEN (NEW.N = 0)
   BEGIN
       dbms_output.put_line('executing temp_air');
   END;
  上例中表示AFTER INSERT行触发器触发的条件是：N字段的值等于0.
  NEW.COLUMN_NAME : INSERT或UPDATE触发器中WHEN语句中引用字段的语法。
  OLD.COLUMN_NAME : 用于UPDATE或DELETE行级触发器中WHEN语句中。在INSERT语句中为Null。
 
Oracle触发器详细介绍四--INSTEAD OF触发器

在简单视图上往往可以执行INSERT、UPDATE和DELETE操作，但是在复杂视图上执行INSERT、UPDATE和DELETE操作是有限的。如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询，那么将禁止在该视图上执行DML操作。为了在这些复杂视图上执行操作，需要建立INSTEAD-OF触发器。INSTEAD-OF触发器具有以下限制：
INSTEAD OF触发器只适用于视图。
INSTEAD OF触发器不能指定BEFORE和AFTER选项。
不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。
INSTEAD OF触发器必须包含有FOR EACH ROW选项。
复杂视图DEPT_EMP用于显示部门号、部门名、雇员号以及雇员名，并且在该复杂视图上不能执行任何DML操作。为了在该视图上执行DML操作，必须建立 INSTEAD OF触发器。下面以完成该认务为例，说明建立INSTEAD OF触发器的方法。在建立INSTEAD OF触发器之前，首先建立视图DEPT_ENP。
create or replace view dept_emp as
  select a.deptno,a.dname,b.empno,b.ename from department a,employee b
where a.deptno=b.deptno;
create or replace trigger tr_instead_of_dept_emp
   instead of insert on dept_emp for each row
   declare
   v_temp int;
   begin
   select count(*) into v_temp from department where deptno=:new.deptno;
   if v_temp=0 then
   insert into department(deptno,dname) values(:new.deptno,:new.dname);
   end if;
   select count(*) into v_temp from employee where empno=:new.empno;
   if v_temp=0 then
   insert into employee(empno,ename,deptno) values(:new.emptno,:new.deptno);
   end if;
   end;
   /

 
Oracle触发器详细介绍五--系统事件触发器

oracle的系统事件触发器：系统事件触发器是指基于oracle系统事件（如logon和startup）所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。
create table jax_event_table(eventname varchar2(30),time date);
createtrigger tr_startup
after startup ondatabase
begin
insertinto jax_event_table values(ora_sysevent,sysdate);
end;
createtrigger tr_shutdown
beforeshutdownondatabase
begin
insertinto jax_event_table values(ora_sysevent,sysdate);
end;
在建立如上所示的两个触发器后，使用shutdown和startup关闭开启数据库会往表jax_event_table中记录一条记录，但 shutdown abort则不会触发该触发器，而startup nomount后使用alter database将数据库更改为mount或者open都只会触发一次。
1          SHUTDOWN     2008-3-20 14:29:47
2          STARTUP         2008-3-20 14:42:52
3          SHUTDOWN     2008-3-20 14:43:06
4          STARTUP         2008-3-20 14:45:34
登录和退出触发器用来记载登录用户名称、时间和ip地址
createtable jax_log_table(
username varchar2(20), log_time date, onoff varchar(6),address varchar2(30));
createtrigger tr_logon
after logon ondatabase
begin
insertinto jax_log_table values(ora_login_user,sysdate,'logon',ora_client_ip_address);
end;
createtrigger tr_logoff
before logoff ondatabase
begin
insertinto jax_log_table values(ora_login_user,sysdate,'logoff',ora_client_ip_address);
end;
select * from jax_log_table;
1          SYS       2008-3-20 14:55:17       logon   
2          SYSMAN           2008-3-20 14:55:21       logon   
3          SYS       2008-3-20 14:55:45       logon    127.0.0.1
4          SYS       2008-3-20 14:56:07       logoff 
5          SYSMAN           2008-3-20 14:56:26       logon   
6          SYSMAN           2008-3-20 14:56:27       logoff 
7          ZHANGLEI       2008-3-20 14:56:35       logon    127.0.0.1
8          ZHANGLEI       2008-3-20 14:57:01       logoff 
9          SYS       2008-3-20 14:57:12       logon    127.0.0.1
10         SYSMAN           2008-3-20 14:57:31       logon   
11         SYSMAN           2008-3-20 14:57:32       logoff 
DDL触发器记录系统所发生的DDL事件（create，alter，drop等）
createtable jax_event_ddl_table(event varchar2(20),
username varchar2(10),owner varchar2(10),objname varchar2(20),
objtype varchar2(10),timedate);
createtrigger tr_ddl
afterddlondatabase
begin
insertinto jax_event_ddl_table values(ora_sysevent,ora_login_user,
ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate)
end;
1          CREATE           SYS       SYS       T2        TABLE 2008-3-20 15:05:41
2          CREATE           SYS       SYS       VIEW_T2          VIEW    2008-3-20 15:06:53
由上面的描述看到，在编写系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下：
Ora_client_ip_address 返回客户端的ip地址
Ora_database_name 返回当前数据库名
Ora_des_encrypted_password 返回des加密后的用户口令
Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
Ora_dict_obj_name_list(name_list out ora_name_list_t) 返回在事件中被修改的对象名列表
Ora_dict_obj_owner 返回ddl操作所对应的对象的所有者名
Ora_dict_obj_owner_list(owner_list out ora_name_list_t) 返回在事件中被修改的对象的所有者列表
Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
Ora_grantee(user_list out ora_name_list_t) 返回授权事件的授权者
Ora_instance_num 返回例程号
Ora_is_alter_column(column_name in varchar2) 检测特定列是否被修改
Ora_is_creating_nested_table 检测是否正在建立嵌套表
Ora_is_drop_column(column_name in varchar2) 检测特定列是否被删除
Ora_is_servererror(error_number) 检测是否返回了特定oracle错误
Ora_login_user 返回登录用户名
Ora_sysevent 返回触发器的系统事件名。