北大青鸟oracle学习笔记26 27 28

数据库触发器

触发器语句
    制定触发器定时、事件、表名及类型
触发器主体
    是pl/sql快或对过程的调用
触发器限制
    可以通过when子句实现

DML(insert update delete)
DDL(create alter drop)
数据库操作(servererror logon logoff startup shutdown)

create trigger 触发器名
before|after insert|delete|update of 列名
on 表名
[for each row]  --行级触发器
when 条件

触发器谓词
inserting insert操作
updating update操作
deleting delete操作
可以根据这三个谓词判断到底在执行哪个操作
create or replace
Trigger tg_insert
before insert or update on student
for each row
begin
  if updating then
    dbms_output.put_line('before update');
  end if;
  if inserting then
    dbms_output.put_line('before insert');
  end if;
end;

 

触发器类型：   
1、 表级触发器（表级触发器对每个DML语句执行一次)
create or replace Trigger tg_insert
before update on student
begin
  dbms_output.put_line('before update);
end;

4 rows updated
before update

2、 行触发器 (行级触发器对DML语句影响的每个行执行一次)
create or replace
Trigger tg_insert
before update on student
for each row  --每行触发一次
begin
  dbms_output.put_line('before update');
end;

4 rows updated
before update
before update
before update
before update

使用触发器时，两个特殊值:new 新插入的值 :old 原来的值  不能用于表级触发器

 create or replace trigger trig_job
before insert or update of job
on emp
for each row
begin
if inserting then
:new.job:=upper(:new.job);
else
:new.job:=upper(:new.job);
end if;
end;

 

3、INSTEAD OF触发(此触发器是在视图上而不是在表上定义的触发器,它是用来替换所使用实际语句的触发器.) 只能用于行级，并且不能在表上创建，只能在视图中创建，实际中可以将对视图的操作转换为多实体表的操作
create or replace
trigger insteadtg
instead of insert or update on stu_view
for each row
begin
  dbms_output.put_line(:new.stu_name);
  dbms_output.put_line('instead of insert or update');
end;

4、 模式触发器

select * from user_objects

CREATE TABLE dropped_obj (
    obj_name VARCHAR2(30),
    obj_type VARCHAR2(20),
    drop_date DATE);

CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
   INSERT INTO dropped_obj
   VALUES( ORA_DICT_OBJ_NAME,
    ORA_DICT_OBJ_TYPE, SYSDATE);
END;

drop trigger trigger_name;---------为了测试可以删除一个不用的trigger或其它数据对象
SELECT * FROM dropped_obj;

 

5、 数据库级触发器

CREATE TABLE system.session_info (
   username   VARCHAR2(30),
   logontime  DATE,
   session_id VARCHAR2(30),
   ip_addr    VARCHAR2(30),
   hostname   VARCHAR2(30),
   auth_type  VARCHAR2(30)
 );

 CREATE OR REPLACE TRIGGER trg_session_info
 BEFORE LOGOFF
 ON DATABASE
 DECLARE
   session_id VARCHAR2(30);
   ip_addr    VARCHAR2(30);
   hostname   VARCHAR2(30);
   auth_type  VARCHAR2(30);
   logontime  date;
 BEGIN
   SELECT sys_context (/'USERENV/', /'SESSIONID/')  -- 会话编号
   INTO session_id FROM dual;
   --  用户登录的客户端IP地址   
   SELECT sys_context (/'USERENV/', /'IP_ADDRESS/')
   INTO ip_addr  FROM dual;
   --  用户登录的客户端主机名    
   SELECT sys_context (/'USERENV/', /'HOST/')
   INTO hostname FROM dual;
   --  登录认证方式，数据库认证或外部认证
   SELECT sys_context (/'USERENV/', /'AUTHENTICATION_TYPE/')
   INTO auth_type FROM dual; 
   INSERT INTO system.session_info
   VALUES (user, sysdate, session_id, 
    ip_addr, hostname,auth_type);
 END;

 

--使用when条件
CREATE OR REPLACE TRIGGER biu_emp_deptno
BEFORE INSERT OR UPDATE OF deptno
ON emp
FOR EACH ROW
WHEN (New.deptno <> 40)
BEGIN
  :New.comm := 0;
END;

变异表
当前正由DML语句修改
声明级联删除

约束表(在触发器中select的表)
对于sql语句，触发语句直接读取
对于声明的引用完整性约束条件，间接读取

表的编译或约束只是对会话而言的

需要明确的是trigger中SQL语句不能进行如下操作：
1）读或修改触发语句的任何变异表,其中包括触发表本身
2）读或修改触发表的约束表中的主关键字，唯一关键字和外部关键字列。
除此之外的其他列都可以修改。

create or replace
TRIGGER TG_DELETE
AFTER DELETE ON STUDENT
FOR EACH ROW
DECLARE

type RefstuCur is ref cursor return student%rowtype;
  stuCur RefstuCur;
  sturow student%rowtype;
  icount INTEGER;
BEGIN
  select count(*) into icount from student;
  dbms_output.put_line(icount);
   open stuCur for select * from student;
   loop
    fetch stuCur into sturow;
    exit when stuCur%NotFound;
    dbms_output.put_line(sturow.stu_name);
  end loop;
END;

SQL Error: ORA-04091: 表 PUF.STUDENT 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "PUF.TG_DELETE", line 7
ORA-04088: 触发器 'PUF.TG_DELETE' 执行过程中出错

这里把for each row去除就可以执行了。

猜想原因可能是在每行更新或删除的过程中，无法修改或读取变异表，而去掉for each row以后在更新或删除操作结束以后，则可以正常修改和读取。详细原因还是不理解。。。

为了更好的阐述变异表考虑下面3个表：students,classes,registered_students 表students和classes都没有依赖关系但是表registered_students有两个外键一个依赖于students表的主键一个依赖于classes的主键

表registered_students 上声明了2个引用完整性约束，因此表students和classes都是registered_students 的约束表，由于存在这种约束所以表students和classes可能会需要使用DML语句进行更新和查询，另外在registered_students表上执行DML语句的时候它就变成一个变异表

触发器中sql语句不能：读取或更新触发语句的任何变异表也包括触发表本身，读取或更新该触发表的约束表的主键列、唯一性键列或外键列但是如果需要也可以更新其他列。这些限制约束适用于所有行级触发器。但有个特例就是如果INSERT只影响一行记录那么定义在这行上的行级BEFORE和AFTER触发器就不会将这个触发表当作变异表。

这里参考了一篇文章：

作为实例可以考虑下面这个触发器即使他更新了students和classes这两个表但是依然是合法的因为更新的列都不是主键列

create or replace trigger cascadersinserts
  before insert on registered_students
  for each row
declare
  v_credits  classes.num_credits%type;
begin
  select num_credits into v_credits from classes where department=:new.department and course=:new.course;
  update students set current_credits=current_credits+v_credits where id=:new.student_id;
  update classes set current_students=current_students+1 where department=:new.department and course=:new.course;
end cascadersinserts;

 

变异表示例

假定希望将每一门主修课程的学生人数限制在5人，可以在students表上定义一个行级触发器来实现这个任务

create or replace trigger limitmajors
  before insert or update of major on students
  for each row
declare
  v_maxstudents constant number:=5;
  v_currentstudents number;
begin
  select count(*) into v_currentstudents from students where major=:new.major;
  if v_currentstudents+1>v_maxstudents then
     raise_application_error(-20000,'too many students in major'||:new.major);
  end if;
end limitmajors;

 

初看之下似乎实现了这个目标但是会产生错误原因是触发器查询了它自己的触发表而该触发表又是一个变异表

那么要想实现就意味这不能在行级触发器里查询该表，但是可以在语句级触发器里查询它。但是不能简单的将它设计成一个语句级触发器因为我们需要在触发器主体中使用:new.major的值。这个问题的解决办法就是创建2个触发器一个行级的一个语句的，在行级里记录:new.major的值但是不查询表，查询在语句里实现。那么怎么记录值呢，就要通过一个包了。

create or replace package studentdata as 
  type t_majors is table of students.major%type index by binary_integer; 
  type t_ids is table of students.id%type index by binary 
  v_studentmajors t_majors; 
  v_studentids    t_ids; 
  v_numbertries   binary_integer:=0; 
end studentsdata;

create or replace trigger rlimitmajors 
  before insert or update of major on students 
  for each row 
begin 
  studentdata.v_numbertries:=studentdata.v_numbertries+1; 
  studentdata.v_studentmajors(studentdata.v_numberies):=:new.major; 
  studentdata.v_studentids(studentdata.v_numberies):=:new.id; 
end rlimitmajors;

create or replace trigger slimitmajors 
  after insert or update of major on students 
declare 
  v_maxstudents constant number:=2; 
  v_currentstudents number; 
  v_studentid student.id%type; 
  v_major students.major%type; 
begin 
  for v_loopindex in 1..studentdata.v_numberies loop 
      v_studentid:=studentdata.v_studentids(v_loopindex); 
      v_major:=studentdata.v_studentmajors(v_loopindex); 
  select count(*) into v_currentstudents from students where major=v_major; 
  if v_currentstudents>v_maxstudents then 
     raise_application_error(-20000,'too many students for major '||v_major||'because of student'||v_studentid); 
  end if; 
  studentdata.v_numberies:=0; 
end slimitmajors;

 

这样就得到了我们想要的结果了！

实际上运用了全局数组保存需要的参数，然后在所有操作执行完以后利用表级触发器最后进行检查，达到发现选课人数过多的情况，从而使编程人员可以进行回滚处理等等。

 

维护：
                             Alter trigger …..disenable;     使得触发器不可用；
                             Alter trigger ……enable;      开启触发器；