触发器

一、语句触发器
create or replace trigger trigger_name
       { before | after } event1 [or event2 or event3]
on table_name
PL/SQL Block;

1、建立BEFORE语句触发器
create or replace trigger tr_sec_emp
       before insert or update or delete on scott.emp
begin
     if to_char(sysdate,'dy','nls_date_language=AMERICAN') in ('sat','sun') then
        raise_application_error(-20001,'不能在休息日更改雇员信息');
     end if;
end;
/

2、建立并使用AFTER语句触发器
create table scott.aud_upd_table
      (host varchar2(30),
       statement varchar2(100),
       exectime date);
/
create or replace trigger tr_upd_emp
      after update on scott.emp
declare
       sql_txt ora_name_list_t;
       v_stmt varchar2(100);
       n binary_integer;
begin
     n:=ora_sql_txt(sql_txt);
     for i in 1..n loop
         v_stmt:=v_stmt||sql_txt(i);
     end loop;
     insert into scott.aud_upd_table values(sys_context('userenv','host'),v_stmt,sysdate);
end;
/

3、使用条件谓词
create or replace trigger tr_sec_emp
before insert or update or delete on scott.emp
begin
     if to_char(sysdate,'dy','nls_date_language=american') in ('sat','sun') then
        case
            when inserting then
                 raise_application_error(-20001,'insert');
            when updating then
                 raise_application_error(-20002,'update');
            when deleting then
                 raise_application_error(-20003,'delete');
         end case;
     end if;
end;
/

 

二、行触发器
create or replace trigger trigger_name
      {before | after } event1 or event2 or event3
on table_name for each row [when condition]
PL/SQL Block;

1、建立BEFORE 行触发器
create or replace trigger tr_emp_sal
       before update of sal on scott.emp
for each row
begin
     if :new.sal<:old.sal then>        raise_application_error(-2000,'工资只能涨');
     end if;
end;
/

2、建立AFTER 行触发器
create table audit_emp_change
      (name varchar2(10),
       oldsal number(6,2),
       newsal number(6,2),
       time date);
/
create or replace trigger tr_sal_change
after update of sal on scott.emp
for each row
declare
       v_temp int;
begin
     select count(*) into v_temp from audit_emp_change where name=:old.ename;
     if v_temp=0 then
        insert into audit_emp_change
               values (:old.ename,:old.sal,:new.sal,sysdate);
     else
         update audit_emp_change set ldsal=:old.sal,newsal=:new.sal,time=sysdate
                where name=:old.ename;
     end if;
end;
/

3、限制行触发器
create or replace trigger tr_sal_change
after update of sal on scott.emp
for each row
when (old.job='SALESMAN')
declare
       v_temp int;
begin
     select count(*) into v_temp from audit_emp_change where name=:old.ename;
     if v_temp=0 then
        insert into audit_emp_change
               values (:old.ename,:old.sal,:new.sal,sysdate);
     else
         update audit_emp_change set ldsal=:old.sal,newsal=:new.sal,time=sysdate
                where name=:old.ename;
     end if;
end;
/

开发实例
1、控制数据安全
create or replace trigger tr_emp_time
before insert or update or delete on scott.emp
begin
     if to_char(sysdate,'hh24') not between '9' and '17' then
        raise_application_error(-20101,'非工作时间');
     end if;
end;
/
2、实现数据审计
create or replace trigger tr_delete_emp
after delete on scott.emp
for each row
begin
     insert into audit_delete_emp values(:old.ename,sysdate);
end;
/
3、实现数据完整性
create or replace trigger tr_dcheck_sal
before update of sal on scott.emp
for each row
when (new.sal1.2*old.sal)
begin
     raise_application_error(-20001,'工资只能升，但不能超过20%');
end;
/
4、实现参照完整性
create or replace trigger tr_update_cascade
before update of deptno on scott.dept
for each row
begin
     update scott.emp set deptno=:new.deptno where deptno=:old.deptno;
end;
/

三、INSTEAD OF 触发器(只适用于视图)

create or replace view dept_emp as
       select a.deptno,a.dname,b.empno,b.ename from scott.dept a,scott.emp b
       where a.deptno=b.deptno;
/
create or replace trigger tr_instead_of_dept_emp
instead of insert on scott.dept_emp
for each row
declare
       v_temp int;
begin
     select count(*) into v_temp from scott.dept where deptno=:new.deptno;
     if v_temp=0 then
        insert into scott.dept (deptno,dname)
               values(:new.deptno,:new.dname);
     end if;
     select count(*) into v_temp from scott.emp where empno=:new.empno;
     if v_temp=0 then
        insert into scott.emp (empno,ename,deptno)
               values(:new.empno,:new.ename,:new.deptno);
     end if;
end;
/

四、事件触发器
1、建立登陆触发器(审计登陆用户的名称、时间、IP)
create table aud_logon_tab
       (username varchar2(20),time date,addr varchar2(20));
/
create or replace trigger tr_logon
after logon on database
begin
     insert into aud_logon_tab
            values(ora_login_user,sysdate,ora_client_ip_address);
end;
/

2、建立DDL触发器(审计在SCOTT方案上的DDL事件)
create table aud_ddl_tab
       (veent varchar2(20),
       username varchar2(10),
       owner varchar2(10),
       objname varchar2(20),
       objtype varchar2(10),
       time date);
/

create or replace trigger tr_ddl
after ddl on scott.schema
begin
     insert into aud_ddl_tab values
           (ora_sysevent,ora_login_user,ora_dict_obj_owner,
            ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
/

五、维护触发器
1、显示触发器信息
select trigger_body from user_triggers where trigger_name='TR_UPD_EMP';

2、禁止触发器
alter trigger tr_upd_emp disable;

3、激活触发器
alter trigger tr_upd_emp enable;
alter table ... enable all triggers;

4、重新编译触发器
alter tirgger tr_upd_emp compile;

5、删除触发器
drop trigger tr_check_sal;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693791/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693791/