【安博培训技术】Oracle8 触发器20130912

Oracle8 触发器


回顾
子程序是命名的 PL/SQL 块，存储在数据库中，可带参数并可在需要时随时调用
有两种类型的PL/SQL子程序，即过程和函数
过程用户执行特定的任务，函数用于执行任务并返回值
程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
程序包由两部分组成，即包规范和包主体
使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳


目标 
理解和应用触发器
熟练掌握基本的触发器用法


触发器
触发器是当特定事件出现时自动执行的存储过程
特定事件可以是执行更新的DML语句和DDL语句
触发器不能被显式调用
触发器的功能：
自动生成数据
自定义复杂的安全权限
提供审计和日志记录
启用复杂的业务逻辑
[思路]回顾SQLServer中对于Trigger的认识：概念、事件后触发、原理（inserted和deleted临时表）
[说明]学员一定要清楚触发器的概念、原理
Oracle中提供更为强大的Trigger，关注不同


创建触发器的语法
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;
[说明] 
After|Before：事件发生前后
Instead of：执行Trigger代码代替导致触发起调用的事件
Referencing：指定新行和旧行（默认为NEW、OLD，对比inserted和deleted）
For each row：行级Trigger
When：满足条件才执行（如对新旧数据值的检查）


1.设置emp2为不能修改：
create or replace trigger tri_emp2 
before insert or update or delete on emp2
begin
raise_application_error(-20000,'不能修改数据');
end;


触发器的组成部分 3-1
触发器由三部分组成：
触发器语句（事件）
定义激活触发器的 DML 事件和 DDL 事件
触发器限制
执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行
[思路]可先举ATM业务的例子，说明一个简单的触发器
Create table t_account(
f_id char(3) primary key,
f_name char(8) not null,
f_balance number(10,2)
);
Create table t_trans(
f_id char(3) not null,
f_date date not null,
f_type char(1) not null,
f_money number(10,2)
);
[注]Deposit、Withdraw(存、取)
Insert into t_account values(‘001’,’张三’,1000);
编写存储过程，当插入一条交易记录时，自动修改账户余额


触发器的组成部分 3-2 
触发器操作
create or replace trigger tr_emp_sal
before update of sal on emp2
for each row
begin
    if :new.sal < :old.sal then
       raise_application_error(-20001,'不能减少员工工资');
    end if ;
end;


触发器的组成部分 3-3
BEFORE 触发器的工作原理
更新表，保存更新到Oracle数据库活着激活触发器。


创建触发器
CREATE OR REPLACE TRIGGER trig_stu
BEFORE UPDATE OF f_class
ON martin.t_student
FOR EACH ROW
WHEN (OLD.f_class=1)
BEGIN
  :NEW.f_class := 1;
END;
/
[例] 学生表中班级为1的学员不可调班
update t_student set f_class=2 where f_id='001';


触发器类型 6-1
触发器的类型有：
触发器类型
模式(DDL)触发器
数据库级触发器
DML触发器
行级触发器
语句级触发器
INSTEAD OF触发器


触发器类型 6-2
DDL 触发器
在模式中执行 DDL 语句时执行
数据库级触发器
在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器
在对表或视图执行DML语句时执行
语句级触发器
无论受影响的行数是多少，都只执行一次
行级触发器
对DML语句修改的每个行执行一次
INSTEAD OF 触发器
用于用户不能直接使用 DML 语句修改的视图


[说明]重点讲述行级触发器的用法，通过三个例子来加深对行级触发器的印象；数据库级触发器了解即可，不讲用法


触发器类型 6-3
行级触发器
SQL> create table t_temp(f_id number,f_name varchar2(20));
SQL> create sequence seq_tempid;
SQL> create trigger trig_temp
before insert or update of f_id
on t_temp
for each row
begin
  if inserting then
    select seq_tempid.nextval into :New.f_id from dual;
  else
    Raise_Application_Error(-20020,'不允许更新ID值！');
  end if;
end;
/


[例]自动维护表的自增型字段
--experiment
insert into t_temp values(1,'Tomato');
insert into t_temp(f_name) values('Potato');
insert into t_temp(f_name) values('Cucumber');
insert into t_temp(f_name) values('Cabbage');
select * from t_temp;
update t_temp set f_id=1 where f_name='Tomato';


触发器类型 6-4
语句级触发器
SQL> create or replace trigger trig_stu
after insert or delete or update
on t_student
begin
  if inserting then
    dbms_output.put_line('已添加t_student中的数据');
  elsif deleting then
    dbms_output.put_line('已删除t_student中的数据');
  elsif updating then
    dbms_output.put_line('已更新t_student中的数据');
  end if;
end;
/


[说明]对每个DML语句执行一次，不常用于与数据相关的活动，通常用于强制实施在表上执行操作的额外安全性措施
PL/SQL块中不能引用New和Old，不使用When子句
[例]对t_student进行写操作时给出提示
-experiment
delete from t_student where f_id='012';
update t_student set f_sex='m' where f_id='001';


触发器类型 6-5
INSTEAD OF 触发器
SQL> CREATE OR REPLACE TRIGGER upd_ord_view
          INSTEAD OF UPDATE ON ord_view
          FOR EACH ROW
          BEGIN
      UPDATE order_master
          SET vencode=:NEW.vencode 
WHERE orderno = :NEW.orderno;
DBMS_OUTPUT.PUT_LINE(‘已激活触发器');
          END;
           /
[说明]只能在视图上定义；只能用于行级触发器；（两个’仅’学员要了解）
代替所使用实际语句；克服了Oracle对视图修改的限制；
[说明] instead of触发器不要求掌握，有兴趣的可参考下面的例子
[例]在视图v_grade定义触发器，添加数据时同时修改三个基表数据（先说明思路，在具体写代码）
--创建视图
create or replace view v_grade as
select f_stuid,f_sex,t_student.f_name as stuname,f_courseid,t_course.f_name as coursename,f_grade
from t_student,t_course,t_grade 
where t_grade.f_stuid=t_student.f_id and t_grade.f_courseid=t_course.f_id;
--Instead of触发器
create or replace trigger trig_v_grade
instead of insert on v_grade
for each row
declare
  Cursor cur_student Is select * from t_student where f_id=:New.f_stuid;
  Cursor cur_course Is select * from t_course where f_id=:New.f_courseid;
  Cursor cur_grade Is select * from t_grade where f_stuid=:New.f_stuid and f_courseid=:New.f_courseid;
  rec_student cur_student%rowtype;
  rec_course cur_course%rowtype;
  rec_grade cur_grade%rowtype;
begin
  Open cur_student;
  Open cur_course;
  Open cur_grade;
  Fetch cur_student into rec_student;
  Fetch cur_course into rec_course;
  Fetch cur_grade into rec_grade;
  if cur_student%notfound then
    insert into t_student(f_id,f_sex,f_name) values(:New.f_stuid,:New.f_sex,:New.stuname);
  else
    update t_student set f_sex=:New.f_sex,f_name=:New.stuname where f_id=:New.f_stuid;
  end if;


  if cur_course%notfound then
    insert into t_course(f_id,f_name) values(:New.f_courseid,:New.coursename);
  else
    update t_course set f_name=:New.coursename where f_id=:New.f_courseid;
  end if;


  if cur_grade%notfound then
    insert into t_grade(f_stuid,f_courseid,f_grade) values(:New.f_stuid,:New.f_courseid,:New.f_grade);
  else
    update t_grade set f_grade=:New.f_grade where f_stuid=:New.f_stuid and f_courseid=:New.f_courseid;
  end if;


  Close cur_student;
  Close cur_course;
  Close cur_grade;
end;
/
--experiment
insert into v_grade values('001','m','zhangsf','10','OOAD',80);


触发器类型 6-6
模式触发器
SQL> create table t_droppedobj
          (
            f_objname varchar2(20),
            f_objtype varchar2(20),
            f_dropdate date
          );


SQL> create or replace trigger trig_dropobj
           after drop on schema
           begin
             insert into t_droppedobj values
             (Ora_Dict_Obj_Name,Ora_Dict_Obj_Type,Sysdate);
           end;
/


模式触发器：DDL语句，阻止DDL操作以及发生DDL操作时提供额外的安全控制；
                   只要在任何模式对象上进行指定DDL操作就会激活触发器
[说明]对于Ora_Dict_Obj_Name等系统变量引导学员到参考文档《Oracle9i.The.Complete.Reference》中查找
[例]记录被删除的表信息
--记录删除模式对象信息的表
--自动记录删除模式对象信息
--experiment
create table t_temp (f_1 char);
drop table t_temp;
select * from t_droppedobj;
drop table t_grade;


启用、禁用和删除触发器
启用和禁用触发器
SQL> ALTER TRIGGER trig_stu DISABLE;
SQL> ALTER TRIGGER trig_stu ENABLE;
删除触发器
SQL> DROP TRIGGER trig_stu;


查看有关触发器的信息
USER_TRIGGERS 数据字典视图包含有关触发器的信息
SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS
WHERE TABLE_NAME=‘t_student';


SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = ‘trig_tempid';


总结
触发器的原理
触发器类型
触发器执行顺序
触发器的管理