一、触发器概述
与表,视图,模式,或者数据库相关的PL/SQL过程,当触发条件被触发时,自动执行
分类:
1.语句触发器
2.行触发器
二、语句触发器
1. 什么是语句触发器
语句触发器,是指当执行DML语句时被隐含执行的触发器,如果在表上针对某种DML操作建立了语句触发器,那么当执行DML操作时会自动执行触发器的相应代码
当审计DML操作或者确保DML操作安全执行时,可以使用语句触发器,使用语句触发器时,不能记录列数据的变化
2. 语法:
create [or replace] trigger trigger_name
timing event1[or event2 or event3]
on table_name
pl/sql block;
trigger_name:用于指定触发器名
timing:指定触发时机(before或after)
event:指定触发事件(INSERT,UPDATE,和DELETE);
table_name:用于指定DML操作对应的表名
3. 语句触发器的分类
3.1 BEFORE触发器
为了确保DML操作在正常情况下执行,可以基于DML操作建立BEFORE语句触发器,例如为了禁止工作人员在休息日改变雇员信息,开发人员可以建立BEFORE语句触发器,以实现数据的安全保护
示例如下:
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate, 'DY', 'nls_date_language = AMERICAN') in
('SAT', 'SUN') then
raise_application_error(-, '不能在工作日修改员工信息');
end if;
end;
修改系统日期:
[root@oracle oracle]#
[root@oracle oracle]# date
Wed Sep :: CST
[root@oracle oracle]# date -s "Wed Sep 6 22:37:27 CST 2014"
Sat Sep :: CST
[root@oracle oracle]# date
Sat Sep :: CST
提示:
SQL>update emp set sal=sal+ where empno=;ORA-:不能在工作日修改员工信息
3.2 使用条件谓词:
当在触发器中同时包含多个触发条件(INSERT,UPDATE,DELETE)时,为了在触发器源代码中区分具体
的触发条件,可以使用三个条件谓词,可以使用以下三个条件谓词:
INSERTING:当触发事件是insert操作时,该条件谓词返回值为TRUE,否则为FALSE
UPDATING:当触发事件是UPDATE操作时,该条件谓词返回值为TRUE,否则为FALSE
DELETING:当触发事件是DELETE作时,该条件谓词返回值为TRUE,否则为FALSE
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate, 'DY', 'nls_date_language = AMERICAN') in
('SAT', 'SUN') then
case
when inserting then
raise_application_error(-, '不能在工作日增加员工');
when updating then
raise_application_error(-, '不能在工作日修改员工');
when deleting then
raise_application_error(-, '不能在工作日解雇员工');
end case;
end if;
end;
执行过程:
SQL> update emp set sal=sal+ where empno=;
update emp set sal=sal+ where empno=
ORA-: 不能在非工作室更新员工
ORA-: 在 "SCOTT.TR_SEC_EMP", line
ORA-: 触发器 'SCOTT.TR_SEC_EMP' 执行过程中出错
3.3 建立after语句触发器:
为了审计DML操作或者DML操作之后执行汇总运算,可以使用after触发器例如,为了审计在emp表上insert,update,delete操作次数,可以建立after触发器
在建立after触发器之前,首先建立审计表audit_table
创建审计表:
create table audit_table(name varchar2(),ins int,upd int,del int,
starttime date,endtime date);
创建触发器:
create or replace trigger tr_audit_table
after insert or delete or update on emp
declare
v_temp int;
begin
select count(*) into v_temp from audit_table where name = 'EMP';
if v_temp = then
insert into audit_table values ('EMP', , , , SYSDATE, null);
end if;
case
when inserting then
update audit_table set ins = ins + where name = 'EMP';
when deleting then
update audit_table set del = del + where name = 'EMP';
when updating then
update audit_table set upd = upd + where name = 'EMP';
end case;
end;
执行结果:
SQL> update emp set sal=sal+ where empno=;
row updatedSQL> select * from audit_table;
NAME INS UPD DEL STARTTIME ENDTIME
---------- --- --- --- ----------- -----------
EMP //