精通Oracle10编程SQL(13)开发触发器

/*
 *开发触发器
 */
--得到日期是周几
select to_char(sysdate+4,'DY','nls_date_language=AMERICAN') from dual;

select to_char(sysdate,'DY','nls_date_language=AMERICAN') from dual;

--建立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;

--在建立了触发器tr_sec_emp之后，如果是星期六、星期日在EMP表上执行DML操作，则会显示错误信息
update emp set sal=sal*1.1 where deptno=1;

--使用条件谓词
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(-20002,'不能在休息日更新雇员');
       when deleting then
          raise_application_error(-20003,'不能在休息日解雇雇员');
     end case;
  end if;
end;

--建立AFTER语句触发器
--为了审计DML操作，或者在DML操作之后执行汇总运算，可以使用AFTER语句触发器
--例如，为了审计在EMP表上INSERT，UPDATE和DELETE的操作次数，可以建立AFTER触发器
--在建立AFTER触发器之前，首先建立审计表audit_table
CREATE TABLE audit_table(
  name VARCHAR2(20),ins int,upd int,del int,starttime date,endtime date);
  
--为了审计在EMP表上DML操作执行的次数、最早执行时间和最近执行时间，需要建立AFTER语句触发器
CREATE OR REPLACE TRIGGER tr_aduit_emp
after insert or update or delete on emp
declare
   v_temp int;
begin
   select count(*) into v_temp from audit_table where 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=ins+1,endtime=sysdate where name='EMP';
      when updating then
          update audit_table set upd=upd+1,endtime=sysdate where name='EMP';
      when deleting then
          update audit_table set del=del+1,endtime=sysdate where name='EMP';
   end case;
end;

--在建立了触发器tr_audit_emp之后，在EMP表上执行DML操作时，都会将DML操作次数以及时间段记录在审计表audit_table中
update emp set sal=2000 where empnoo=7788;
update emp set sal=2000 where empnoo=1111;
select * from audit_table;

select * from emp;

--行触发器
--行触发器是指执行DML操作时，每作用一行就触发一次的触发器
--建立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之后，如果雇员新工资低于其原有工资，则会提示错误信息。
update emp set sal=80 where empno=7788;

select * from emp;

--建立AFTER行触发器
--下面以审计雇员工资变化为例，说明使用AFTER行触发器的方法
--在建立触发器之前，首先应建立存放审计数据的表audit_emp_change
CREATE TABLE audit_emp_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 
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 oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
   end if;
end;

--在建立触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_empO_change中
update emp set sal=sal*1.1 where deptno=1;

select * from audit_emp_change;

select * from emp;

--限制行触发器
--当使用行触发器时，默认情况下会在每个被作用行上执行一次触发器代码
--为了使得在特定条件下执行行触发器代码，就需要使用WHERE子句对触发条件加以限制
--下面以审计岗位为"manager"的雇员工资变化为例，说明限制行触发器的方法
CREATE OR REPLACE TRIGGER tr_sal_change
AFTER UPDATE OF sal ON emp
for each row
when (upper(old.job)=upper('manager'))
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 oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
   end if;
end;

--当建立触发器tr_sal_change时，因为使用WHERE子句指定了触发条件，所以只有在满足触发条件时才会执行触发器代码
update emp set sal=sal*1.1 where deptno=2;


--DML触发器使用注意事项
--当编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据。
--例如，如果要基于EMP表建立触发器，那么该触发器的执行代码不能包含对EMP表的查询操作
--尽管在建立触发器时不会出现任何错误，但在执行相应触发操作时会显示错误信息。
--假定希望雇员工资不能超过当前的最高工资，并使用触发器实现该规则
CREATE OR REPLACE TRIGGER tr_emp_sal
before update of sal on emp
for each row
declare
   maxsal number(6,2);
begin
   select max(sal) into maxsal from emp;
   if :new.sal>maxsal then
      raise_application_error(-20010,'超出工资上限');
   end if;
end;

--如上所示，当建立触发器tr_emp_sal时，不会显示任何错误。但因为触发器代码引用了基表emp，所以在执行UPDATE操作时会出错
update emp set sal=6000 where empno=7788;


--使用DML触发器
--为了确保数据库数据满足特定的商业规则或企业逻辑，可以使用约束、触发器和子程序实现。因为约束性能最好，实现最简单，所以首选约束
--如果使用约束不能实现特定规则，那么应该选择触发器
--如果触发器仍然不能实现特定规则，那么应该选择子程序（过程和函数）
--DML触发器可以用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能

--控制数据安全
--在服务器级控制数据安全是通过授予和收回对象权限来完成的,如
grant select,insert,update,delete on emp to smith;

--为了实现更复杂的安全模型（例如限制要修改的数据、修改时间等），就需要使用DML触发器了
--下面以限制用户在正常工作时间（9：00-17：00）改变EMP表数据为例，说明使用DML触发器控制数据安全的方法
create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
   if to_char(sysdate,'HH24') not between '9' and '17' then
      raise_application_error(-20101,'非工作时间');
   end if;
end;

--建立了触发器tr_emp_time之后，只能在9：00-17：00之间在EMP表上执行DML操作，如果不在该时间段，则会显示错误信息
update emp set sal=3200 where empno=7788;

--实现数据审计
--审计可以用于监视非法和可疑的数据库活动，ORACLE数据库本身提供了审计功能
--例如，如果要对EMP表上的DML操作进行审计，可以执行如下命令
AUDIT INSERT,UPDATE,DELETE ON emp;
--如上所示，在设置了审计选项之后，如果在EMP表上执行INSERT、UPDATE和DELETE操作，ORACLE会将关于SQL操作的信息（用户、时间等）写入到数据字典中。
--注意，使用数据库审计只能审计SQL操作，而不会记载数据变化。
--为了审计SQL操作所引起的数据变化，必段要使用DML触发器
CREATE OR REPLACE TRIGGER tr_sal_change
after update of sal on 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 oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
   end if;
end;

--在建立了触发器tr_sal_change之后，当修改雇员工资时，会将每个雇员的工资变化全部写入到审计表audit_emp_change中
update emp set sal=sal*1.1 where deptno=1;

select * from audit_emp_change;

select * from emp;


--实现数据完整性
--数据完整性用于确保数据库数据满足特定的商业逻辑或企业规则，数据完整性可以使用约束、触发器和子程序实现。因为约束的实现最简单，性能也最好
--所以实现数据完整性首选约束。
--例如，为了限制雇员工资不能低于800元，可以选用CHECK约束。示例如下：
alter table emp add constraint ck_sal check(sal>=800);

--但某些情况下使用约束无法实现特定的商业规则，此时可以使用触发器来实现数据完整性。
--例如，假定希望雇员的新工资不能低于其原工资，但也不能高出原工资的20%，使用约束显然无法实现该规则，但通过触发器却可以实现该项规则
CREATE OR REPLACE TRIGGER tr_check_sal
before update of sal on emp
for each row
when (new.sal<old.sal or new.sal>1.2*old.sal)
begin
   raise_application_error(-20931,'工资只升不降，并且升幅不能超过20%');
end;

--在建立了触发器tr_check_sal之后，如果雇员新工资不符合相应规则，则会提示错误信息
update emp set sal=sal*1.25 where empno=7788;

--实现参照完整性
--参照完整性是指若两个表之间具有主从关系（也即主外键关系），当删除主表数据时，必须确保相关的从表数据已经被删除
--当修改主表的主键列数据时，必须确保相关的从表数据已经被修改。
--为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字
--示例如下：
ALTER TABLE emp add constraint fk_deptno
  foreign key(deptno) references dept(deptno)
  on delete cascade;
  
--当用如上方式建立了外部键约束fk_deptno之后，在删除主表DEPT的数据时，会同时删除从表EMP的所有相关数据。
--但使用约束却不能实现级联更新，如果要更新DEPT表的部门号，则会显示错误信息

update dept set deptno=5 where deptno=1;

--原因是EMP表包含有该部门的相应雇员。为了实现级联更新，可以使用触发器，示例如下：
CREATE OR REPLACE TRIGGER tr_update_cascade
after update of deptno on dept
for each row
begin
  update emp set deptno=:new.deptno where deptno=:old.deptno;
end;

--在建立了触发器tr_update_cascade之后，当更新DEPT表的部门号时，会级联更新EMP表的相应雇员的部门号
update dept set deptno=5 where deptno=1;

select ename from emp where deptno=5;

select * from dept;
select * from emp;

delete dept where deptno >=7;


--建立INSTEAD OF触发器
--对于简单视图，可以直接执行 INSERT、UPDATE和DELETE操作
--但是对于复杂视图，不允许直接执行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操作，必须要基于视图建立INSTEAD-OF触发器
--在建立了INSTEAD-OF触发器之后，就可以基于复杂视图执行INSERT，UPDATE和DELETE语句。但建立INSTEAD-OF触发器有以下注意事项
--1.INSTEAD OF选项只适用于视图
--2.当基于视图建立触发器时，不能指定BEFORE和AFTER选项
--3.在建立视图时没有指定WITH CHECK OPTION选项
--4.当建立INSTEAD OF触发器时，必须指定FOR EACH ROW选项

--建立复杂视图dept_emp
--视图是逻辑表，本身没有任何数据。视图只是对应于一条SELECT语句，当查询视图时，其数据实际是从视图基表上取得。
--为了简化部门及其雇员信息的查询，应建立复杂视图dept_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.deptno;

--当执行以上语句建立了复杂视图dept_emp之后，直接查询视图dept_emp会显示部门及其雇员信息，但不允许执行DML操作
select * from dept_emp where deptno=1;

--建立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;

--当建立了INSTEAD-OF触发器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');

select * from dept_emp;

select * from dept;

select * from emp;


--建立系统事件触发器
--建立例程启动和关闭触发器
create table event_table(event varchar2(30),time date);
--例程启动触发器和例程关闭触发器只有特权用户才能建立，并且例程启动触发器只能使用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之后，在关闭例之前，会执行该触发器的相应代码，但SHUTDOWN ABORT命令不会触发该触发器
shutdown

startup

select event,to_char(time,'YYYY/MM/DD HH24:MI') time from event_table;


--建立登录和退出触发器
--首先建立专门存放登录和退出的信息表LOG_TABLE
create table log_table(username varchar2(20),logon_time date,logoff_time date,address varchar2(20));

--注意，登录触发器和退出触发器一定要以特权用户身份建立，并且登录触发器只能使用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;

create or replace trigger tr_logoff
before logoff on database
begin
   insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);
end;

--在建立了触发器tr_logon之后，当用户登录到数据库之后，会执行其触发器代码
--在建立了触发器tr_logoff之后，当用户断开数据库连接之前，会执行其触发器代码
select * from log_table;

--建立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);

--注意，当建立DDL触发器时，必须要使用AFTER关键字
CREATE OR REPLACE TRIGGER tr_ddl
after ddl on haiya1.schema
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之后，如果在haiya1方案对象上执行了DDL操作，则会将该信息记载到表event_ddl中
create table bjtemp(cola int);
drop table bjtemp;

select event,owner,objname from event_ddl;

select * from event_ddl;


--管理触发器
--显示触发器信息
select trigger_name,status from user_triggers where table_name='EMP';

select * from user_triggers where table_name='EMP';

--禁止触发器
alter trigger tr_check_sal disable;

--激活触发器
alter trigger tr_check_sal enable;

--禁止或激活表的所有触发器
--如果在表上同时存在多个触发器，那么使用ALTER TABLE命令可以一次禁止或激活所有触发器
alter table emp disable all triggers;
alter table emp enable all triggers;

--重新编译触发器
--当使用ALTER TABLE命令修改表结构（例如增加列、删除列）时，会使得其触发器转变为INVALID状态。
--在这种情况下，为了使得触发器继续生效，需要重新编译触发器
alter trigger tr_check_sal compile;

--删除触发器
drop  trigger tr_check_sal;