一、语句触发器
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/