PL/SQL 触发器

触发器的一个明显的特性就是不能被显式地调用,当触发事件发生时就会隐式地执行该触发器,而且触发器是不接收参数的。

触发器本身就是一个命名的语句块。

1.完成表的变更校验

2.自动数据库维护

3.控制数据库管理活动


触发器组成部分:

1.触发器触发的事件

2.触发事件所在的对象

3.触发器触发的条件

4.触发器被触发时所要执行的语句块,或称触发器体,是一个包括SQL语句和PL/SQL语句的过程调用或PLSQL块,或者是被封装在PLSQL块中的java程序.


触发器定义示例

create or replace tregger t_verifysalary

before update on emp for each row when(new.sal>old.sal)

declare

v_sal number;

begin

if updating('sal') then

v_sal:=:NEW.sal - :OLD.sal;

delete from emp_history where empno=:OLD.empno;

insert into emp_history values (:OLD.empno,:OLD.ename,:OLD.job,:OLD.mgr,:OLD.hiredate,:OLD.sal,:OLD.comm,:OLD.deptno);

update emp_history set sal=v_sal where empno=:NEW.empno;

end if;

end;


触发器的分类

1.行触发器与语句触发器:行触发器会对数据库表中的每一行触发一次触发器代码,语句触发器则仅触发一次,与语句所影响的行数无关。

2.before触发器与alfter触发器:是指与触发时机相关的触发器。

3.instead of触发器:又称为替代触发器,是指不直接执行触发语句,一般用在视图更新的场合。

4.系统事件触发器与用户事件触发器:在发生系统级的事件时。比如数据库启动,服务器错误消息时间触发时。

5.DML触发器

6.系统触发器:对数据库实例或某个用户模式进行操作时的触发器,因此可以定义数据库系统触发器和用户触发器

7.替代触发器:当对视图进行操作时定义的触发器。



DML触发器


1.单行触发器执行顺序

当在某一行上定义了多个触发器时

1.before语句触发器

2.before行级触发器

3.执行DML

4.after行级触发器

5.after语句触发器


2.多行触发器执行顺序

如果触发器影响到多行,那么在每一行上都要执行一次触发器语句,假定触发器影响到两行,则其执行顺序

1.before语句触发器

2.第一行的before行触发器

3.第一行执行DML

4.第一行after行级触发器

5.第二行before行级触发器

6.第二行执行DML

7.第二行alfter行级触发器

8.after语句触发器


在使用update作为触发行为时,还可以使用update of 来指定一个或多个字段

before update of empno,ename,sal on emp


示例记录日志触发器

创建一个日志记录表,当用户对emp表进行新增,修改或删除时,会将修改记录记录到这个日志表中,以便知道对emp表的更改历史记录。

create table emp_log(

log_id number,

log_action varchar2(100),

log_date DATE,

empno number(4),

ename varchar2(10),

job varchar2(18),

mgr number(4),

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number(2)

);


create or replace trigger t_emp_log

after insert or delete or update on emp for each row

begin

if inserting then

insert into emp_log values(emp_seq.nextval,'INSERT',sysdate,:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);

insert into emp_log values(emp_seq.nextval,'UPDATE_NEW',sysdate,:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);

insert into emp_log values(emp_seq.currval,'UPDATE_OLD',sysdate,:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

elsif deleting then

insert into emp_log values(emp_seq.nextval,'DELETE',:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);

end if;

end;



使用语句触发器

如果在创建触发器时,不指定for each row子句,那么创建的触发器就是语句触发器,否则为行触发器。语句触发器每次触发器触发时仅执行一次.


1.使用before语句触发器

使用语句触发器限制修改

对emp表的更改只能在正常工作日的8:30~18:00之内,不再这个时间的修改都不能进行

create or replace trigger t_verify_emptime

before insert or delete or update on emp

begin

if(TO_CHAR(sysdate,'DAY') IN ('星期六','星期天')) or (TO_CHAR(sysdate,'HH24:MI') NOT BETWEEN '08:30' ADN '18:00')

then

raise_application_error(-20001,'不能在非常时间段内操纵emp表');

end if;

end;


2.使用after语句触发器

after语句触发器在所有的触发器都执行完成之后,最后被触发,在这个阶段可以进行一些审计工作,比如统计自触发器添加到现在以来所执行过的dml语句的次数和最后执行的时间,以便于根据这个结果进行性能的分析。

create table audit_table(

table_name varchar2(20),

ins_count int,

udp_count int,

del_count int,

start_time date,

end_time date

);


create or replace trigger t_audit_emp

after insert or update or delete on emp

declare

v_temp int;

begin

select count(*) into v_temp from audit_table where table_name='EMP';

if v_temp=0

then

insert into audit_table values('EMP',0,0,0,SYSDATE,NULL);

end if;

case

when inserting then

update audit_table set ins_count=ins_count+1,end_time=sysdate where table_name='EMP';

when updating then

update audit_table set udp_count=udp_count+1,end_time=sysdate where table_name='EMP';

when deleting  then

update audit_table set del_count=del_count+1,end_time=sysdate where table_name='EMP';

end case;

end;


使用OLD和NEW谓词

1.当在insert语句上激发触发器时,OLD结构是不包含任何值的

2.当在update语句上激发触发器时,OLD和NEW结构都是具有值,OLD包含在更新之前记录的值,NEW包含了在更新之后记录的值。

3.当在DELETE语句上激发触发器时,NEW结构不包含任何值,OLD结构包含已经被删除的记录

4.NEW和OLD谓词也包含了rowid伪列,这个伪列在OLD和NEW结构中具有相同的值。

5.不能更改OLD结构的值,如果这样做会触发ORA-04085错误。但是可以修改NEW结构的值

6.在触发器内部,不能将NEW或OLD结构作为一个记录参数传递给过程或函数,仅能传递单个的字段

7.当在匿名块或触发器内部使用NEW和OLD谓词时,必须要在前面加上冒号。

8.在NEW和OLD结构中不能进行记录级别的操作,比如直接为记录赋值是非法的。


示例

create table emp_data

(

emp_id int,

empno number,

ename varchar2(20)

);


create table emp_data_his

(

emp_id int,

empno number,

ename varchar2(20)

);


create or replace trigger t_emp_data

before insert on emp_data for each row

declare

emp_rec emp_data%ROWTYPE;

begin

select emp_seq.nextval into :NEW.emp_id from dual;

emp_rec.emp_id:=:NEW.emp_id;

emp_rec.empno:=:NEW.empno;

emp_rec.ename:=:NEW.ename;

insert into emp_data_his values emp_rec;

end;



使用referencing子句

在触发器中也可以使用referencing子句来更改默认的谓词名称,比如可以将new子句更改为emp_new,将old子句更改为emp_old这样的别名。

在指定了别名后,就可以在触发体中使用:old_name和:new_name来代替:OLD和:NEW谓词。

create or replace trigger t_vsal_ref

before update on emp

referencing OLD as emp_old NEW as emp_new for each row when(emp_new.sal>emp_old.sal)

declare

v_sal number;

begin

if updating ('sal') then

v_sal:=:emp_new.sal - :emp_old.sal;

delete from emp_history where empno=:emp_old.sal;

insert into emp_history values(:emp_old.empno,:emp_old.ename,:emp_old.job,:emp_old.mgr,:emp_old.hiredate,:emp_old.sal,:emp_old.comm,:emp_old.deptno);

update emp_history set sal=v_sal where empno=:emp_new.empno;

end if;

end;



使用when子句

when子句是在触发器被触发后,用来控制是否执行触发体代码的一个控制条件,在when子句中可以使用不带冒号的new和old谓词访问记录的值,可以使用复合条件表达式组织多条记录。

create or replace trigger t_emp_comm

before update on emp for each row when(new.comm>old.comm)

declare

if updating ('comm') then

v_comm:=:NEW.comm - :OLD.comm;

delete from emp_history where empno=:OLD.empno;

insert into emp_history values(:OLD.empno,:OLD.ename,:OLD.mgr,:OLD.hiredate,:OLD.sal,:OLD.comm,:OLD.deptno);

update emp_history set comm=v_comm where empno=:NEW.empno;

end if;

end;



使用条件谓语

条件谓语主要用来确定触发器的DML语句的类型。

1.INSERTING

2.UPDATING

3.DELETING


使用UPDATING谓词判断特定字段的更新

create or replace trigger t_comm_sal

before update on emp for each row

begin

case

when updating('comm') then

if :NEW.comm<:OLD.comm then

raise_application_error(-20001,'新的comm值不能小于旧的comm值');

end if;

when updating('sal') then

if :NEW.sal<:OLD.sal then

raise_application_error(-20001,'新的sal值不能小于旧的sal值');

end if;

end case;

end;



控制触发顺序

使用FOLLOWS子句

create table trigger_data

trigger_id int,

trigger_name varchar2(100)

);


create or replace trigger one_trigger

before insert  on trigger_data for each row

begin

:NEW.trigger_id:=:NEW.trigger_id+1;

dbms_output.put_line('触发了one_trigger');

end;


create or replace trigger two_trigger

before insert on trigger_data for each row follows one_trigger  --让该触发器在one_trigger后面触发

begin

dbms_output.put_line('触发了two_trigger');

if :NEW.trigger_id > 1

then

:NEW.trigger_id:=:NEW.trigger_id+2;

end if;

end;


实际上应用follows子句后,在两个触发器之间创建了依赖。使得two_trigger依赖于one_trigger,可以通过以下sql语句查询

sql>select referenced_name,referenced_type,dependency_type from user_dependencies where name='TWO_TRIGGER' and referenced_type='TRIGGER';



触发器限制

在编写触发器时,需要注意不能对触发其所应用的基表中读取或修改数据。

1.通常 行级别的触发器不能读写触发器所作用的基表,这个限制仅应用在行级触发器上,但是语句级的触发器可以*地读写触发器基表

2.如果在触发器中使用自治事务,并在触发体中提交事务,则可以查询基表的内容,但是不能对基表进行任何的修改操作。



使用自治事务

1.如果在触发器中抛出一个异常,将导致整个事务回滚

2.如果在触发体中使用了DML操作,比如像日志表中插入日志记录,那么这些DML操作也属于主事务的一部分,因此触发体中任何意外操作也会导致整个事务回滚

3.在触发体中不能使用commit或rollback语句,因为这会影响到主事务的执行


create or replace trigger t_emp_comm

before update on emp for each row when(NEW.comm>OLD.comm)

declare

v_comm number;

pragma autonomous_transaction;

begin

if updating ('comm') then

v_comm := :NEW.comm - :OLD.comm;

delete from emp_history where empno=:OLD.comm;

insert into emp_history values(:OLD.empno,:OLD.ename,:OLD.job,:OLD.mgr,:OLD.hiredate,:OLD.sal,:OLD.comm,:OLD.deptno);

update emp_history set comm=v_comm where empno=:NEW.empno;

end if;

commit;

exception 

when others then

rollback;

end;



替代触发器

替代触发器是触发器类型中的另外一种,这种触发器只能定义在视图上,当要对一个不能进行修改的视图进行数据修改的时候,或者要修改视图中的某个嵌套表的列时,可以使用替代触发器。


替代触发器,又称为instead of 触发器,之所以取这个名字,是因为触发器将替代原来的数据操作语句的执行,更改为使用在触发器中定义的语句来执行数据操作。

在学习视图时曾经了解到,一些简单的单表视图,可以直接对其应,insert,update或delete语句进行更新,但是对于一些复杂的视图,比如当视图符合以下任何一种时,不能进行DML操作:

1.在定义视图的查询语句中使用了集合操作符,比如UNION,UNION ALL,INTERSECT,MINUS等

2.在视图中使用了分组函数,比如MIN,MAX,SUM,AVG,COUNT等

3.使用了GROUP BY,CONNECT BY或START WITH等子句

4.具有DISTINCT关键字

5.使用了多表连接查询

如果要对这种视图进行修改,可以通过在视图上编写一个替代触发器来完成正确的工作,这样就允许对它进行修改了。

当用户进行DML进行视图操作时候,通过替代触发器,将这些DML语句对视图的更改替换成对基表的DML操作。注意事项:

1.替代触发器只能用于视图

2.当建立替代触发器时,不能指定before和after选项

3.当对视图建立替代触发器时,必须指定for each row方法


创建视图

create or replace view scott.emp_dept(empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc)

as

select emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm,emp.deptno,dept.dname,dept.loc from dept,emp where ((dept.deptno=emp.deptno));


替代触发器

create or replace trigger t_dept_emp

instead of insert on emp_dept

referencing new as n for each row

declare

v_counter int;

begin

select count(*) into v_counter from dept where deptno=:n.deptno;

if v_counter=0

then

insert into dept values(:n.deptno,:n.dname,:n.loc);

end if;

select count(*) into v_counter from emp where empno=:n.empno;

if v_counter=0

then

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (:n.empno,:n.ename,:n.job,:n.mgr,:n.hiredate,:n.sal,:n.comm,:n.deptno);

end if;

end;


替代触发器必须使用for each row,表明对视图的操作是一个行级的触发器。



UPDATE与DELETE替代触发器

create or replace trigger t_dept_emp_update

instead of update on emp_dept referencing NEW as n OLD as o

for each row

declare

v_counter int;

begin

select count(*) into v_counter from dept where deptno=:o.deptno;

if v_counter>0

then

update dept set dname=:n.dname,loc=:n.loc where deptno=:o.deptno;

end if;

select count(*) into v_counter from emp where empno=:n.empno;

if v_counter>0

then

update emp set ename=:n.ename,job=:n.job,mgr=:n.mgr,hiredate=:n.hiredate,sal=:n.sal,comm=:n.comm,deptno=:n.deptno where empno=:o.empno;

end if;

end;


DELETE触发器

create or replace trigger t_dept_emp_delete

instead of update on emp_dept

referencing OLD AS o

for each row

begin

delete from emp where empno=:o.empno;

delete from dept where deptno=:o.deptno;

end;


替代触发器完整示例

create or replace trigger t_emp_dept

instead of update or insert or delete on emp_dept

referencing NEW as n OLD as o

for each row

declare

v_counter int;

begin

select count(*) into v_counter from dept where deptno=:o.deptno;

if v_counter>0

then

case

when updating then

update dept set dname:=n.dname,loc=:n.loc where deptno=:o.deptno;

when inserting then

insert into dept values(:n.deptno,:n.dname,:n.loc);

when deleting then

delete from dept where deptno=:o.deptno; 

end case;

end if;

select count(*) into v_counter from emp where empno=:n.empno;

if v_counter>0

then

case

when updating then

update emp set ename=:n.ename,job=:n.job,mgr=:n.mgr,hiredate=:n.hiredate,sal=:n.sal,comm=:n.comm,deptno=:n.deptno where empno=:o.empno;

when inserting then

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (:n.empno,:n.ename,:n.job,:n.mgr,:n.hiredate,:n.sal,:n.comm,:n.deptno);

when deleting then

delete from emp empno=:o.empno;

end case;

end if;

end;



嵌套表替代触发器

如果在视图的表列中使用了嵌套表,在要对视图进行更新时,必须使用替代触发器

仅在定义的视图中包含的嵌套表列中才能使用替代触发器,只有使用THE()或TABLE()子句来修改视图所包括的嵌套表上的列时,触发器才会触发。当视图上的DML语句被执行时,触发器不会被触发.


创建用于嵌套表的对象类型

create or replace type emp_obj as object(

empno number(4),

ename varchar2(10),

job   varchar2(10),

mgr   number(4),

hiredate DATE,

sal   number(7,2),

comm  number(7,2),

deptno number(2)

);

嵌套表类型

create or replace type emp_tab_type as table of emp_obj;

嵌套表视图,MULTISET必须与cast一起使用

create or replace view dept_emp_view as 

select deptno,dname,loc,cast(MULTISET(select * from emp where deptno=dept.deptno) as emp_tab_type) emplst from dept;

不能直接在嵌套表视图执行DML


创建嵌套表替代触发器

create or replace dept_emp_innerview

instead of insert

on nested table emplst of dept_emp_view

begin

insert into emp(deptno,empno,ename,job,mgr,hiredate,sal,comm) values(:PARENT.deptno,:NEW.empno,:NEW.ename,:NEW.job,:NEW.mgr,:NEW.hiredate,:NEW.sal,:NEW.comm);

end;


代码中使用了PARENT谓词获取嵌套表父行的deptno部门编号。

insert into table(select emplst from dept_emp_view where deptno=10) values (8003,'四爷','皇上',NULL,SYSDATE,5000,500,10);


嵌套表替代触发器与普通的替代触发器的创建方式基本相同,但是有如下两个基本的区别

1.嵌套表使用"on nested table" 嵌套表列 of 嵌套表视图 这种定义方式

2.parent 谓词在嵌套表替代触发器中具有值,指向包含嵌套表的视图的父项记录。




系统时间触发器

DML触发器和替代触发器都是在DML事件上触发的,相反,系统触发器是在DDL事件和数据库服务器事件时触发的,DDL包含create,alert或drop等语句,使得数据库管理人员可以监控对数据库的更改。


示例:如果要知道在scott下,创建表时的各类信息,可以通过一个DDL触发器,通过监控对CREATE语句的应用来实现。

在scott用户下创建一个保存DDL创建信息的表

create table created_log

(

obj_owner varchar2(30),

obj_name  varchar2(30),

obj_type varchar2(20),

obj_user varchar2(30),

created_date DATE

);


create or replace trigger t_created_log

after create on scott.schema

begin

insert into scott.created_log(obj_owner,obj_name,obj_type,obj_user,create_date) values(sys.dictionary_obj_owner,sys.dictionary_obj_name,sys.dictionary_obj_type,sys.login_user,SYSDATE);

end;



触发器可以在DATABASE或SCHEMA级别进行定义,这两个关键词用来确定系统触发器的级别。

startup 和 shutdown触发器只能在database级别上创建,在方案级别上创建没有意义,因此不会被触发。


创建两个触发器,都用来监控用户的LOGON事件,一个在方案级别触发,一个在数据库级别触发。

create table log_db_table

(

username varchar2(20),

logon_time DATE,

logoff_time DATE,

address varchar2(20)

);


create table log_user_table

(

username varchar2(20),

logon_time DATE,

logoff_time DATE,

address varchar2(20)

);


以DBA登录创建DATABASE级别的LOGON事件触发器

create or replace trigger t_db_logon

after logon on database

begin

insert into log_db_table(username,logon_time,address) values(ora_login_user,SYSDATE,ora_client_ip_address);

end;

以scott登录,创建SCHEMA级别的事件触发器

create or replace trigger t_user_logon

after logon on schema

begin

insert into log_user_table(username,logon_time,address) values(ora_login_user,SYSDATE,ora_client_ip_address);

end;


database级的记录所有与数据库连接相关的记录。



触发器属性列表

oracle在DBMS_STANDARD包中提供了一些功能性的函数,以便在开发系统级别的触发器时可以提供一些系统级别的信息。


示例:startup和shutdown触发器

create table event_table(

sys_event varchar2(30),

event_time DATE

);


create or replace trigger t_startup

after startup on database

begin

insert into event_table values(ora_sysevent,SYSDATE);

end;


create or replace trigger t_startup

before shutdown on database

begin

insert into event_table values(ora_sysevent,SYSDATE);

end;



属性函数使用示例

在属性函数列表中,ora_is_drop_column和ora_is_alter_column是两个非常有用的属性函数,在很多场合,可能希望一些表的字段不能被修改或移除,这样在多人开发时可以防止开发人员的意外操作而出现意外,此时可以考虑创建alter或drop系统触发器时,使用这两个属性函数来避免用户进行非法的删除。


create or replace trigger preserve_app_cols

after alter on schema

declare

cursor curs_get_columns(cp_owner varchar2,cp_table varchar2)

is

select column_name from all_tab_columns where owner=cp_owner and table_name=cp_table;

begin

if ora_dict_obj_type ='TABLE'

then

for v_column_rec in curs_get_columns(

ora_dict_obj_owner,

ora_dict_obj_name

);

loop

if ora_is_alter_column(v_column_rec,column_name)

then

if v_column_rec.column_name='EMPNO' then

raise_application_error(-20003,'不能对empno字段进行修改');

end if;

end if;

end loop;

end if;

end;



定义SERVERERROR触发器

servererror事件可以用来跟踪数据库中发生的错误,错误代码可以通过server_error属性函数在触发器内部得到,可以通过该函数确定堆栈中的错误代码,可以使用DBMS_UTILITY.FORMAT_ERROR_STACK获取错误信息。

使用after servererror时必须要了解如下的错误是否会被触发

ORA-00600:oracle内部错误

ORA-01034:oracle不可用

ORA-01403: 没有找到数据

ORA-01422:提取操作返回大于请求的行数

ORA-01423:在一个提取操作中检测到额外的行

ORA-04030:在分配字节时内存不够。


after servererror触发器在触发器内部产生异常时也不会触发,这样会导致死循环。


使用after servererror触发器记录错误日志

create table servererror_log(

error_time date,

username varchar2(30),

instance number,

db_name varchar2(50),

error_stack varchar2(2000)

);


create or replace trigger t_logerrors

after servererror on database

begin

insert into servererror_log values(sysdate,login_user,instance_num,database_name,DBMS_UTILITY.format_error_stack);

end;


触发器的事务与约束

1.startup和shutdown触发器不可以有任何条件,不能使用when子句

2.servererror触发器可以用errno测试来检查具体的错误

3.logon和logoff触发器可以用userid或username测试来检查用户的标识符或用户名

4.DDL触发器可以使用when子句检查正被修改的对象的类型和名称,并且可以检查用户标识符或用户名。



触发器的管理

查询emp表上定义了哪些触发器

select trigger_name,trigger_type,table_name,triggering_event, status from user_triggers

where table_name='EMP';


如果想要创建一个一开就是被禁用的触发器

create or replace trigger t_temp_testing

after insert on emp

disable

begin

NULL;

end;



触发器名称与权限的管理

触发器的命名具有自己的名称空间,所谓的名称空间是指在这个范围内用于对象名称的合法标识符集,所有这个名称空间内的对象的命名必须唯一。触发器的名称空间与子程序,包和表的名称空间不同。子程序,包和表具有相同的名称空间,因此在一个方案内,如果子程序,包或表任何一个具有相同名称,都会导致不合法的命名。而触发器存在于单独的名称空间,因此可以与表和过程具有相同的名称,都会导致不合法的命名。而触发器存在于单独的名称空间,因此可以与表和过程具有相同的名称,只需要确保在一个方案下面所有的触发器名称不同,而不需要担心与表,子过程和包重名。


触发器是一个存储在数据字典中的方案对象,除了触发器本身要具有一定的访问权限之外,次触发器的所有者必须对触发器所引用的对象具有必要的对象特权,而且这些权限必须被直接赋予,而不能通过角色进行给予。






      本文转自潘阔 51CTO博客,原文链接:http://blog.51cto.com/pankuo/1630256,如需转载请自行联系原作者





上一篇:svn导出项目后报错汇总


下一篇:spring boot druid mybatis 多数据源 配置