触发器和内置程序包.DOC

1、第十二章触发器和内置程序包一、触发器特定事件出现时自动执行的存储过程，不能直接调用1、创建触发器create or replace trigger 触发器名before|after|instead ofinsert|delete|updateof 某列, 某列 . or insert|delete|updateof 某列, 某列 . on 模式. 某表或某视图referencing new as 默认名为 new 的即将更新的行的新名|old as 默认名为 old 的更新前的行的新名for each row when (某条件)declare声明部分;begin可执行部分;exception

2、异常处理部分;end 触发器名;- 在 emp 表中插入行、或更新 deptno 列之前，- 如果新行的 deptno 不等于 40，就把 comm 强行改为 0，- 每行都触发create or replace trigger emp_trgbefore insert or update of deptnoon empfor each rowwhen (new.deptno 40)”（6）触发器主体，触发器操作，PL/SQL 块PL/SQL 块 declare-begin-exception，如“begin :m := 0; end;”只有行级触发器的 PL/SQL 块中的语句能访问行的列值

3、，如 “for each row . :m := 0;”- 在 emp 表中更新 sal 列之后，- 如果新行的 sal 大于旧行的 sal，就输出它们的差值，- 每行都触发create or replace trigger emp_trgafter update of sal on empfor each rowwhen (new.sal - old.sal 0)declarediff number;begindiff := :new.sal - :old.sal;dbms_output.put_line(diff);end emp_trg;- 测试update emp set sal =

4、sal + 100 where empno = 7788;3、触发器类型（1）行级触发器对 DML 语句影响的每个行都执行一次的触发器，有 for each row- 创建序列create sequence seq;- 创建触发器create or replace trigger emp_trg- 如果一个触发器由多种 DML 语句触发before insert or update of empno on empfor each rowbegin- 在触发器主体中用条件谓词 inserting、deleting、updating 判断if inserting then- 用行级触发器给主键插入

5、序列select seq.nexval into :new.empno from dual;elseraise_application_error(-20001, 不许更新 empno);end if;end emp_trg;- 测试insert into emp (empno) values(0);insert into emp (empno) values(0);insert into emp (empno) values(0);select empno from emp;- emp 删一条，向同结构的表 emp_log 写一条- 创建和 emp 同结构的空表 emp_logcreate

6、table emp_log as select * from emp where 1 = 2;- 创建触发器create or replace trigger delete_trgbefore delete on empfor each rowbegininsert into emp_log (empno, ename, sal, deptno)values (:old.empno, :old.ename, :old.sal, :old.deptno);end;- 测试delete emp where empno = 7788;select * from emp_log;rollback;（2

7、）语句级触发器无论 DML 语句影响了多少行，每个 DML 语句只执行一次的触发器，无 for each row- DML 影响很多行后，只给出一句提示create or replace trigger emp_trgafter insert or delete or update on empbeginif inserting thendbms_output.put_line(已插入);elsif deleting thendbms_output.put_line(已删除);elsif updating thendbms_output.put_line(已更新);end if;end emp

8、_trg;- 测试update emp set sal = 3000; - 更新 14 行，但只显示一条“已更新”（3）instead of 触发器on 后面必须是视图必须有 for each row用 PL/SQL 块中的语句替换 DML 语句的触发器，用于修改不能直接用 DML 语句修改的视图DML 语句不执行，触发器主体执行，只用于视图- 通过视图同时向两个表插入数据、如果数据已存在则按主键更新- 创建视图create or replace view emp_view asselect e.empno, e.ename, d.deptno, d.dname from emp e, dep

9、t dwhere e.deptno = d.deptno;- 创建触发器create or replace trigger emp_dept_trginstead of insert on emp_viewfor each rowdeclarecursor ec is select * from empwhere empno = :new.empno;cursor dc is select * from deptwhere deptno = :new.deptno;v_emp ec%rowtype;v_dept dc%rowtype;beginopen ec;open dc;fetch ec

10、into v_emp;fetch dc into v_dept;if dc%notfound theninsert into dept(deptno, dname) values(:new.deptno, :new.dname);elseupdate dept set dname = :new.dname where deptno = :new.deptno;end if;if ec%notfound theninsert into emp(empno, ename) values(:new.empno, :new.ename);elseupdate emp set ename = :new.

11、ename where empno = :new.empno;end if;close ec;close dc;end emp_dept_trg;- 测试insert into emp_view values(9999, MACMIC, 10, GAME);- 通过视图同时向两个表插入数据、如果数据已存在则提示“主键重复”- 创建视图create or replace view emp_view asselect e.empno, e.ename, d.deptno, d.dnamefrom emp e inner join dept don e.deptno = d.deptno;- 创建触

12、发器create or replace trigger emp_dept_trginstead of insert on emp_viewfor each rowdeclarev_temp int;beginselect count(*) into v_temp from deptwhere deptno = :new.deptno;if v_temp = 0 theninsert into dept (deptno, dname)values (:new.deptno, :new.dname);end if;select count(*) into v_temp from empwhere

13、empno = :new.empno;if v_temp = 0 theninsert into emp (empno, ename, deptno)values (:new.empno, :new.ename, :new.deptno);elseraise_application_error(-20001, 主键重复);end if;end emp_dept_trg;- 测试insert into emp_view values(9999, MACMIC, 10, GAME);- 创建视图create or replace view emp_view(total_employeer, tot

14、al_salary, deptno)as select count(*), sum(sal), deptnofrom emp group by deptno- 在视图上删除部门号为 30 的记录delete emp_view where deptno = 30; - error，此视图的数据操纵操作非法- 创建 instead of 触发器create or replace trigger emp_trginstead of delete on emp_viewfor each rowbegindelete emp where deptno = :old.deptno;end;- 在视图上删除

15、部门号为 30 的记录delete emp_view where deptno = 30; - ok（4）模式触发器在模式级的操作上建立的触发器，在模式对象上进行模式级操作时触发触发器模式级的操作：DDL：create、alter、drop、truncateDCL：grant、revoke模式对象：表、视图、索引、序列、同义词、过程、函数、程序包create or replace trigger 触发器名before|after create|alter|drop|truncate|ddl|grant|revoke|dclon 某模式.schemawhen (某条件 )declare声明部分;

16、begin可执行部分;exception异常处理部分;end 触发器名;- 创建表，记录 mac 模式的所有 ddl 操作create table event_ddl(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(10),time date);- 创建 mac 的模式触发器create or replace trigger ddl_trgafter ddl on mac.schemabegininsert into event_ddlvalues

17、 (ora_sysevent, ora_login_user,ora_dict_obj_owner, ora_dict_obj_name,ora_dict_obj_type, sysdate);end ddl_trg;- 创建、删除一个表conn mac/macmacmiccreate table test_table(x char);drop table test_table;- 查看 mac 的模式事件表select * from event_ddl;（5）数据库级触发器在数据库事件上创建的触发器数据库事件是跨模式的、实例范围的，包括：startup、shutdown、服务器错误、logo

18、n（当 connect 时触发） 、logoff（当 disconnect 时触发）- 必须 sys 才有 startup、shutdown 权限conn sys/sys as sysdbaset serveroutput on- 创建触发器create or replace trigger database_trgbefore shutdown on databasebegindbms_output.put_line(数据库即将关闭);end database_trg;- 测试，为什么没触发？shutdown- 创建记录登录、登出信息的表create table log_table(user

19、name varchar2(10),logon_time date,logoff_time date,address varchar2(20);- 创建登录触发器create or replace trigger logon_trgafter logon on databasebegininsert into log_table (username, logon_time, address)values (ora_login_user, sysdate, ora_client_ip_address);end;- 创建登出触发器create or replace trigger logoff_t

20、rgbefore logoff on databasebegininsert into log_table (username, logoff_time, address)values (ora_login_user, sysdate, ora_client_ip_address);end;- 分别用 disc、conn 命令测试登出、登录触发器discconn- 查看触发器插入 log_table 中的登录、登出信息，为什么 ora_client_ip_address 列没数据？select * from log_table;4、触发器限制select 语句必须是 select into、或

21、内部游标声明不许 DDL、 TCL触发器中不能调用包括 TCL 的存储过程事务有原子性，整个触发器是一个完整事务，所以触发器中不能再嵌 TCL- 测试用的表create table t (t1 number primary key,t2 varchar(10);- 带 TCL 的触发器create or replace trigger tr_sec_empbefore insert or update or delete on empbegininsert into t values(1,a);insert into t values(2,a);commit;end tr_sec_emp;-

22、测试delete emp; - error，COMMIT 不能在触发器中:old、:new 都是 rowtype，但是行中不能有 long、longrow 类型的列在 PL/SQL 块的 beginend 之间，只要不是 declare 部分声明的变量，都要用冒号但是 when 条件中的 new、old 不用带冒号语句级触发器的执行次数 = 1行级触发器的执行次数 = n列级触发器 update of 某列, .5、启用和禁用触发器alter trigger 触发器 enable|disable;alter table 表名 enable|disable all triggers; - 触发器

23、属于特定的表6、删除触发器drop trigger 触发器;7、查看触发器信息desc user_triggers;select trigger_name from user_triggers where table_name = 表;select trigger_type, triggering_event, when_clause from user_triggers where trigger_name = 触发器;- 综合例子- 创建表create table dept_summary(deptno number(2),sal_sum number(9, 2),emp_count nu

24、mber);- 插入数据测试表insert into dept_summaryselect deptno, sum(sal), count(*)from emp group by deptno;- 创建存储过程，显示 dept_summary 表create or replace procedure dept_procascursor cur is select * from dept_summary;beginfor v_rec in cur loopdbms_output.put_line(v_rec.deptno|:|v_rec.sal_sum|:|v_rec.emp_count);en

25、d loop;end;- 创建触发器create or replace trigger emp_trgafter insert or update or delete on empbegindelete from dept_summary;insert into dept_summaryselect deptno, sum(sal), count(*)from emp group by deptno;end emp_trg;- 创建 PL/SQL 块，- 用 dbms_utility.exec_ddl_statement(DDL 语句)在 PL/SQL 块中创建触发器，- 在块中向 emp 插

26、入数据，调用存储过程 dept_proc 显示 dept_summay 表中信息begin- 用 dbms_utility.exec_ddl_statement(DDL 语句)在 PL/SQL 块中创建触发器dbms_utility.exec_ddl_statement (create or replace trigger emp_trgafter insert or update or delete on empbegindelete from dept_summary; - 删除 dept_summary 表的旧记录insert into dept_summary - 向 dept_sum

27、mary 表插入新记录select deptno, sum(sal), count(*)from emp group by deptno;end emp_trg;);insert into emp (empno, ename, sal, deptno) - 向 emp 插入数据values (8899, MIC, 8899, 20);dept_proc; - 调用存储过程 dept_proc 显示 dept_summay 表中信息end;二、内置程序包sys 拥有 Oracle 所有私有对象，它们被定义成公有同义词，并把执行权限授予 public 组，所以所有用户都可以不带模式名地访问这些对象

28、- 源代码：c:oracleora92rdbmsadmin*.sqlrem - 注释create or replace package 某内置包 asprocedure 某过程(参数列表);pragma restrict_references(某过程,WNDS,RNDS);end;create or replace public synonym dbms_output for dbms_output - 公有同义词grant execute on dbms_output to public - 执行权限授予 public 组程序包名称 说明standard 和dbms_standard此包内的过程不加“包.”前缀就能调用，如raise_application_error()dbms_lob 操作 blob、clob、bfile 等类型数据dbms_lockdbms_output 从 PL/SQL 块输出信息dbms_sessiondbms_rowiddbms_random 8 位随机正负整数dbms_sql 动态 SQL，DML、DDL，可以返回多行、空行dbms_jobdbms_xmldomdbms_xmlparser