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

数据库  /  管理员 发布于 4年前   300

/* *开发触发器 */--得到日期是周几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_empbefore insert or update or delete on empbegin  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_empbefore insert or update or delete on empbegin  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_tableCREATE 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_empafter insert or update or delete on empdeclare   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_salBEFORE update of sal on empfor each rowbegin   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_changeCREATE TABLE audit_emp_change(   name varchar2(10),oldsal number(6,2),   newsal number(6,2),time date);--为了审计所有雇员的工资变化和雇员工资的更新日期，必须要建立AFTER行触发器CREATE OR REPLACE TRIGGER tr_sal_changeafter update of sal on empfor 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_changeAFTER UPDATE OF sal ON empfor each rowwhen (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_salbefore update of sal on empfor each rowdeclare   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_timebefore insert or update or delete on empbegin   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_changeafter update of sal on empfor each rowdeclare   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_salbefore update of sal on empfor each rowwhen (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_cascadeafter update of deptno on deptfor each rowbegin  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_empCREATE OR REPLACE VIEW dept_emp as select a.deptno,a.dname,b.empno,b.enamefrom dept a,emp bwhere 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_empinstead of insert on dept_empfor each rowdeclare   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_startupafter startup on databasebegin   insert into event_table values(ora_sysevent,sysdate);end;create or replace trigger tr_shutdownbefore shutdown on databasebegin   insert into event_table values(ora_sysevent,sysdate);end;--在建立了触发器tr_startup之后，当打开数据库之后，会执行该触发器的相应代码--在建立了触发器tr_shutdown之后，在关闭例之前，会执行该触发器的相应代码，但SHUTDOWN ABORT命令不会触发该触发器shutdownstartupselect event,to_char(time,'YYYY/MM/DD HH24:MI') time from event_table;--建立登录和退出触发器--首先建立专门存放登录和退出的信息表LOG_TABLEcreate table log_table(username varchar2(20),logon_time date,logoff_time date,address varchar2(20));--注意，登录触发器和退出触发器一定要以特权用户身份建立，并且登录触发器只能使用AFTER关键字，而退出触发器只能使用BEFORE关键字create or replace trigger tr_logonafter logon on databasebegin  insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);end;create or replace trigger tr_logoffbefore logoff on databasebegin   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_ddlafter ddl on haiya1.schemabegin  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;