oracle触发器

触发器的基本操作

创建触发器

在创建触发器时，必须指定触发器的执行时间和触发事件，创建触发器的语法如下（在EMP表中删除记录后触发）：

create or replace trigger emp_count
after delete on emp
declare
cou integer;
begin
select count(*) into cou from emp;
dbms_output.put_line('现在还剩'||cou||'条数据');
end;

查看触发器

创建成功的触发器存放在数据库中，与触发器有关的数据字典有：USER_TRIGGER、ALL_TRIGGER和DBA_TRIGGER等。其中，USER_TRIGGER存放当前用户的所有触发器，ALL_TRIGGER存放当前用户可以访问的所有触发器，DBA_TRIGGER存放数据库中的所有触发器。下面查看当前用户的所有触发器，示例如下：

select * from user_triggers;

修改触发器

修改触发器，只能通过带有or replace选项的create trigger语句重建。而ALTER  TRIGGER语句则用来启用或禁止触发器

改变触发器的状态

触发器有ENABLED（有效）和DISABLE(无效)两种状态。新建的触发器默认是ENABLED状态。启用和禁用触发器的语句是ALTER TRIGGER,其语法如下：

alter trigger trigger_name enabled|disabled

如果使一个表上的所有触发器都有效或无效，可以使用下面的语句：

alter table table_name enabled all triggers;

alter table table_name enabled all triggers;

删除触发器

删除触发器的语法如下：

drop trigger trigger_name;

删除触发器和删除过程或函数不同，过程或函数没有与使用到的数据库对象关联。如果删除过程或函数所使用到的表，那么过程或函数被标记为INVAID状态，仍存在于数据库中。如果删除创建触发器的表或视图，那么也将删除这个触发器。

语句触发器

语句触发器是指当执行DML语句时被隐含执行的触发器。如果在表上针对某种DML操作建立了语句触发器，那么当执行DML操作时会自动执行触发器的相应代码。

建立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(-20001,'不能在休息日改变雇员信息');
end if;
end;

使用条件谓词

当在触发器中同时包含多个触发事件（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(-20001,'不能在休息日增加雇员');
when updating then
raise_application_error(-20001,'不能在休息日更新雇员');
when deleting then
raise_application_error(-20001,'不能在休息日解雇雇员');
end case;
end if;
end;

当建立了触发器tr_sec_emp之后，如果星期六、星期日在EMP表上执行DML操作，则会根据不同的操作显示不同的错误号和错误消息。

触发器的新值和旧值

OLD关键字指数据操作之前的旧值，NEW关键字指数据操作之后的新值。OLD和NEW关键字在使用时必须在其前面加上冒号。OLD值只对UPDATE和DELETE操作有效，对INSERT无效；NEW只对UPDATE和INSERT有效，对DELETE无效。

OLD和NEW关键字只能用于行级触发器（for each row），不能用在语句级触发器，因为在语句级触发器中一次触发涉及许多行，无法指定是哪一个新旧值。

下面创建一个触发器，当更新表EMP中雇员工资的时候触发，显示新旧值。示例如下：

create or replace trigger t_emp
before update on emp
for each row
declare
oldvalue  number;
newvalue number;
begin
oldvalue:=:old.sal;  --数据操作之前的旧值赋值给变量oldvalue
newvalue:=:new.sal;  --数据操作之后的新值赋值给变量newvalue
dbms_output.put_line('OLD:'||oldvalue||'    '||'NEW:'||newvalue);
end;

行级触发器

在创建触发器时，如果使用了for each row 选项，则表示该触发器为行级触发器。行级触发器和语句触发器的区别在于：行级触发器在执行DML操作时，每作用一行就触发一次

建立BEFORE行触发器

在开发数据库应用时，为了确保数据符合商业逻辑或企业规则，应该使用约束对输入数据加以限制，但某些情况下使用约束可能无法实现复杂的商业逻辑或企业规则，此时可以考虑使用BEFORE行触发器。下面以确保雇员工资不能低于其原有工资为例，说明建立BEFORE行触发器的方法。示例如下：

create or replace trigger tr_emp_sal
before update of sal on emp
for each row 
begin
if:new.sal<:old.sal then
raise_application_error(-20010,'工资只涨不降');
end if;
end;

在建立触发器tr_emp_sal之后，如果雇员新工资低于其原有工资，则会提示错误消息(ORA-20010：工资只涨不降)

建立AFTER行触发器

为了审计DML操作，可以使用语句触发器或ORACLE系统提供的审计功能；而为了审计数据变化，则应该使用AFTER行触发器。下面以审计员工工资变化为例，说明使用AFTER行触发器的方法。在建立触发器之前，首先应建立存放审计数据的表EMP_SAL_CHANGE,示例如下：

create table emp_sal_change(
name varchar2(10),
oldsal number(6,2),
newsal number(6,2),
time date
);
-- 为了审计所有员工的工资变化和雇员工资的更新日期，必须要建立AFTER行触发器。示例如下：
create or replace trigger tr_sal_change
after update of sal on emp
for each row   --更新emp表的sal列之后触发
declare
v_temp int;
begin
select count(*) into v_temp from emp_sal_change
where name=:old.ename;
if v_temp=0 then
insert into emp_sal_change
values(:old.ename,:old.sal,:new.sal,sysdate);
else
update emp_sal_change
set oldsal=:old.sal,newsal=:new.sal,time=sysdate
where name=:old.ename;
end if;
end;

在建立了触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写到审计部emp_sal_change中。

限制行触发器

在使用行触发器时，默认情况下会在每个被作用行上执行一次触发器代码。为了使得在特定条件下执行行触发器代码，就需要使用when子句对触发器条件加以限制。以上面示例为例，可以将限制条件加在for each row后。

instead of触发器

对于简单视图，可以直接执行insert、update和delete操作。但是对于复杂视图，不允许直接执行insert、update和delete操作。当视图符合以下任何一种情况时，都不允许直接执行DML操作，具体情况如下：

具有集合操作符（UNION、UNION ALL、INTERSECT、MINUS）；

具有分组函数（MIN()、MAX()、SUN()、AVG()、COUNT()等）；

具有GROUP BY、CONNECT BY或START WITH等子句；

具有DISTINCT关键字；

具有连接查询。

为了在具有以上情况的复杂视图上执行DML操作，必须要基于视图建立INSTEAD OF 触发器。在建立了INSTEAD OF触发器后，就可以基于复杂视图执行DML。但建立INSTEAD OF 触发器有以下注意事项：

INSTEAD OF选项只适用于视图；

当基于视图建立触发器时，不能指定BEFORE和AFTER选项；

在建立视图时不能指定WITH CHECK OPTION选项；

在建立INSTEAD OF触发器时，必须制定FOR EACH ROW选项。

建立复杂视图

视图是逻辑表，本身没有任何数据。视图只是对应于一条select语句，当查询视图时，其数据实际是从视图基表上取得。为了简化部门及其雇员信息的查询，应建立复杂视图DEMP_EMP，示例如下：

create or replace view dept_emp as
select a.deptno,a.dname,b.empno,b.ename
from dept a,emp b
where a.deptno=b.empno;

当执行以上语句建立了复杂视图DEPT_EMP之后，直接查询视图DEPT_EMP会显示部门及其雇员信息，但不允许执行DML操作

建立INSTEAD OF触发器

为了在复杂视图上执行DML操作，必须要基于复杂视图来建立INSTEAD OF触发器。下面以在复杂视图DEPT_EMP上执行INSERT操作为例，说明建立INSTEAD OF触发器的方法。示例如下：

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;

当建立了触发器tr_instead_of_dept_emp之后，就可以在复杂视图DEPT_EMP上执行INSERT操作了，示例如下：

insert into dept_emp values(50,'ADMIN','1223','MARY');
insert into dept_emp values(10,'ADMIN','1224','BAKE');

执行了以上两条INSERT语句之后，就为DEPT表插入了一条数据，为EMP表插入了两条数据。

系统事件触发器

系统事件触发器是指基于Oracle系统事件（例如LOGON和STARTUP）所建立的触发器。通过使用系统事件触发器，提供了跟踪系统或数据库变化的机制。

例程启动和关闭触发器

为了跟踪例程启动和关闭事件，可以分别建立例程启动触发器和例程关闭触发器。为了记载例程启动和关闭的事件和时间，首先建立事件表EVENT_TABLE。示例如下：

create table event_table(
event varchar2(30),
time date
);

在建立了事件表event_table之后，就可以在触发器中引用该表了。例程启动触发器和例程关闭触发器只有特权用户才能建立，并且例程启动触发器只能使用AFTER关键字，而例程关闭触发器只能使用BEFORE关键字。示例如下：

--例程启动触发器
create or replace trigger tr_startup
after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
--例程关闭触发器
create or replace trigger tr_shutdown
before shutdown on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;

在建立了触发器tr_startup之后，当打开数据库之后，会执行该触发器的相应代码；在建立了触发器tr_shutdown之后，在关闭例程之前，会执行该触发器的相应代码。

建立登录和退出触发器

为了记载用户登录和退出事件，可以分别建立登录和退出触发器。为了记载登录用户和退出用户的名称、时间和IP地址，应该首先建立专门存放登录和退出的信息表LOG_TABLE。示例如下：

create table log_table(
username varchar2(20),
logon_time date,
logoff_time date,
address varchar2(20)
);

在建立了log_table表之后，就可以在触发器中引用该表了。注意，登录触发器和退出触发器一定要以特权用户身份建立，并且登录触发器只能使用AFTER关键字，而退出触发器只能使用BEFORE关键字。示例如下：

create or replace trigger tr_logon
after logon on database
begin
insert into log_table(username,logon_time,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;

建立了触发器tr_logon之后，当用户登录到数据库之后，会执行其触发器代码

建立DDL触发器

为了记载系统所发生的DDL事件（create、alter和drop等），可以建立DDL触发器。为了记载DDL事件信息，应该建立专门的表，以便存放DDL事件信息。示例如下：

create table event_ddl(
event varchar2(20),
username varchar2(10),
owner varchar2(10),
objname varchar2(20),
objtype varchar2(10),
time date
);

在建立了表EVENT_DDL之后，就可以在触发器中引用该表了。为了记载DDL事件，应该建立DDL触发器。注意，当建立DDL触发器时，必须要使用AFTER关键字。示例如下：

create or replace trigger tr_ddl
after DDL on scott.schema   --执行DDL语句后触发
begin
insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

在建立了触发器tr_ddl之后，如果在Scott方案对象上执行了DDL操作，则会将信息记载到表event_ddl中。