当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2015-06-29   作者:bijian1013   来源:转载   浏览:
摘要: /* *开发触发器 */ --得到日期是周几 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 O
/*
 *开发触发器
 */
--得到日期是周几
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;

 

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
精通Oracle 10g Pl/SQL编程 PL/SQL是Oracle特有的编程语言,它不仅具有过程编程语言的基本特征(循
触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时
SQLServer 中的触发器是一种特殊的存储过程,而存储过程可以说是对数据库定义的一些“操作”,几天
在上一篇博客中我们学习总结了存储过程,今天我们学习一种特殊的存储过程——触发器。顾名思义,触
触发器 概述 触发器为特殊类型的存储过程,可在执行语言事件时自动生效。SQL Server 包括三种常规类
触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发而被执行
简单介绍: 触发器其实是一种特殊的存储过程,它只有在特定的事件发生时自动执行。存储过程和触发器
在上一篇博客中我们学习总结了存储过程,今天我们学习一种特殊的存储过程——触发器。顾名思义,触
为什么需要触发器 为什么需要触发器(TRIGGER)呢?典型的应用就是银行的取款机系统 假定该系统的数据
触发器 (一)理解: 是一种特殊的存储过程,它不能显式地调用,而是进行insert、delete、update时
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号