PLSQL触发器详解-2.doc

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_id80 )begin :new_mission_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_id80 )限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。其中的new_value是代表更新之后的值。4、 触发操作是触发器的主体begin :new_mission_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, ,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 fooBegin If user not in (DONNY) then Raise_application_error(-20001, You dont have access to modify this table.); End if;End;/即使SYS，SYSTEM用户也不能修改foo表试验对修改表的时间、人物进行日志记录。1、 建立试验表create table employees_copy as select * from hr.employees2、 建立日志表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,Donnyhotmail,sysdate,12);select *from employees_log总结：语句级触发器.(语句级触发器对每个DML语句执行一次）是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。实例:create or replace trigger tri_testafter insert or update or delete on testbeginif updating thendbms_output.put_line(修改);elsif deleting thendbms_output.put_line(删除);elsif inserting thendbms_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 offSQL 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 rowexecuting temp_airSQL INSERT INTO temp SELECT * FROM temp; - insert 1 rowexecuting temp_airSQL INSERT INTO temp SELECT * FROM temp; - inserts 2 rowsexecuting temp_airexecuting temp_airSQL尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。2、行级触发器语法： CREATE OR REPLACE TRIGGER trigger_nameAFTER|BEFORE INSERT|UPDATE|DELETE ON table_nameFOR EACH ROWWHEN (Boolean expression)DECLARE Local declarationsBEGIN Trigger Body written PL/SQLEND; 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 dept a,emp bwhere 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 dept where deptno=:new.deptno; if v_temp=0 then insert into dept(deptno,dname) values(:new.deptno,:new.dname); end if; select count(*) into v_temp from emp where empno=:new.empno; if v_temp=0 then insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno); end if; end; /create or replace trigger tr_update_of_dept_emp instead of update on dept_emp for each row declare v_temp int; begin select count(*) into v_temp from dept where deptno=:new.deptno; if v_temp=0 then update dept set deptno=:new.deptno,dname=:new.dname; end if; select count(*) into v_temp from emp where empno=:new.empno; if v_temp=0 then update emp set empno=:new.empno,ename=:new.ename,deptno=:new.deptno; end if; end; / Oracle触发器详细介绍五-系统事件触发器oracle的系统事件触发器：系统事件触发器是指基于oracle系统事件（如logon和startup）所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。create table jax_event_table(eventname varchar2(30),time date);createtrigger tr_startupafter startup ondatabasebegininsertinto jax_event_table values(ora_sysevent,sysdate);end;createtrigger tr_shutdownbeforeshutdownondatabasebegininsertinto 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:472 STARTUP 2008-3-20 14:42:523 SHUTDOWN 2008-3-20 14:43:064 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_logonafter logon ondatabasebegininsertinto jax_log_table values(ora_login_user,sysdate,logon,ora_client_ip_address);end;createtrigger tr_logoffbefore logoff ondatabasebegininsertinto 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 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 8 ZHANGLEI 2008-3-20 14:57:01 logoff 9 SYS 2008-3-20 14:57:12 logon 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 var