pl/sql编程----触发器

触发器 （trigger）
触发器指隐含的执行的存储过程，既当特定事件出现时自动执行的存储过程。
当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert，update，delete语句，而触发操作实际就是一个pl/sql块

触发器的功能：自动生成数据，
自定义复杂的安全权限，
提供审计和日志记录，
启用复杂的业务逻辑

触发器类型：
模式（DDL）触发器，执行DDL语句时执行
数据库级触发器，在发生打开，关闭，登录，退出数据库等系统事件时执行
DML触发器（行级触发器：对影响的每个行执行一次
语句级触发器：无论受影响的行数是多少，只执行一次
instead of 触发器 ：用于用户不能直接使用DML语句修改的视图
）

限制：触发器中不能使用控制语句（commit，rollback，savepoint）
由触发器所调用的过程或函数也不能使用控制语句
触发器中不能使用long,long raw类型

当触发器被触发时，要使用被插入，更新或删除的记录中的列值，有时要使用操作前，后列的值。
实现：
:new 修饰符访问操作完成后列的值
:old修饰符访问操作前完成前列的值

使用create trigger 命令创建
语法：
create or replace trigger 名
after/before/instead of
insert/[or]update of column/[or]delete
on 表名
[for each row ] --行触发器，每触发一行时触发
[when (condition)]
pl/sql块

before类型触发器

 脚本：
     --删除表
        drop table book;
       --创建表book
        create table book (bid number,bname varchar2(50),bhouse varchar2(50),pinym varchar2(50));
        --插入数据
        insert into book(bid,bname,bhouse,pinym) values(1,'笑傲江湖','人民出版社','xajh');
        insert into book(bid,bname,bhouse,pinym) values(2,'钢铁是怎样炼成的','人民出版社','gtszylcd');
        insert into book(bid,bname,bhouse,pinym) values(3,'平凡的世界','人民出版社','pfdsj');
        commit;
        
案例：before类型
--创建触发器，在插入一条记录或者修改了bid这个字段的时候将pinym修改为大写
--注意，如果这里我们修改的不是bid这个字段，则不会触发触发器，如果需要，只需要将of bid去掉
create or replace trigger tri_01 
before insert or update of bid 
on book
for each row
  begin
    :new.pinym :=upper(:old.pinym);
    end;
--测试
update book set bid = 3 where bid = 3
案例：before类型，条件类型
--创建触发器，在插入一条记录或者修改了bid这个字段的时候如果改变的这条记录pinym的字符长度大于5，修改出版社名称
create or replace trigger tri_01 
before insert or update of bid 
on book
for each row
   when (length(new.pinym)>5)
   --此处new不需要在前面加冒号
  begin
    --:new.pinym :=upper(:old.pinym);
      :new.bhouse := '繁花似锦出版社';
    end;
--测试
insert into book(bid,bname,bhouse,pinym) values(4,'鲁宾逊漂流记','人民出版社','lbxplj');

after类型触发器

案例：after类型
--创建触发器，在删除一条记录的时候，将这条记录备份在另一张表中
脚本：
--创建表
create table bbook as select * from book where 1<0;
--创建触发器
create or replace trigger tri_02 
after delete on book
for each row
  begin
    insert into bbook(bid,bname,bhouse,pinym) values(:old.bid,:old.bname,:old.bhouse,:old.pinym);
    end;

触发器谓词：
oracle提供了三个参数，inserting，updateing，deleting，用于判断触发了哪些操作
inserting，updateing，deleting如果触发的语句为，inset，update，delete语句，则为true，否则为false
我们可以根据这在写日志，追踪
案例：

--创建日志表
create table test_log(myuser number(3),mytype varchar(20),mydate date );
--创建序列
create  sequence xul;
--需要用到序列来实现序号的自动递增
--创建触发器 
create or replace trigger test_1
after delete or update or insert
on book
declare
  v_type test_log.mytype%type;
begin
    if inserting then
      v_type := 'insert';
      dbms_output.put_line('记录成功插入，记录到日志');
      insert into test_log(myuser,mytype,mydate) values (xul.nextval,v_type,sysdate);
    elsif deleting then   v_type := 'delete';
        dbms_output.put_line('记录成功删除，记录到日志');
        insert into test_log(myuser,mytype,mydate) values (xul.nextval,v_type,sysdate);
    elsif updating then   v_type := 'update';
      dbms_output.put_line('记录成功修改，记录到日志');
      insert into test_log(myuser,mytype,mydate) values (xul.nextval,v_type,sysdate);
    end if;
end;

案例：统计book表，并且在book进行删除，更新，插入操作时可以同步更新
脚本：

--创建表book
        create table book (bid number,bname varchar2(50),bhouse varchar2(50),pinym varchar2(50));
        --插入数据
        insert into book(bid,bname,bhouse,pinym) values(1,'笑傲江湖','人民出版社','xajh');
        insert into book(bid,bname,bhouse,pinym) values(2,'钢铁是怎样炼成的','人民出版社','gtszylcd');
        insert into book(bid,bname,bhouse,pinym) values(3,'平凡的世界','人民出版社','pfdsj');
        commit;
--创建统计表
create table tongj as 
       select bhouse,count(*) sum from book group by bhouse
--创建触发器
--原理：在进行操作之后，触发器将原来的统计表数据删除，使用游标将新统计的数据插入到统计表中，来实现同步更新
create or replace trigger zd_tongj 
after insert or update or delete on book
declare
--使用游标来进行数据存储
--游标的作用是用来存储多行数据
cursor C0 is select bhouse,count(*) sum from book group by bhouse;
 begin 
   delete from tongj;
   for C1 in C0 loop
     dbms_output.put_line('出版社:'||C1.bhouse||'数量:'||C1.sum);
     insert into tongj(bhouse,sum)values(C1.bhouse,C1.sum);
     end loop;
      close C0;
     end;
    /*
    --另一种循环方法创建触发器
    create or replace trigger zd_tongj
after insert or update or delete on book
declare
cursor C0 is select bhouse,count(*) sum from book group by bhouse;
C2 tongj.bhouse%type;
C3 tongj.sum%TYPE;
 begin
   delete from tongj;
   open C0;
 loop
   FETCH C0 INTO C2, C3;
    exit when C0%notfound;
      dbms_output.put_line('出版社:'||C2||'数量:'||C3);
      insert into tongj(bhouse,sum)values(C2,C3);
  end loop; 
   close C0;
end;*/
    --测试     
    delete from book where bid = 3

instead of 触发器
它主要用在视图上，当视图涉及到多张表，或者有统计函数不能进行修改时，我们是不能进行修改的，如果要修改，就要使用instead of 触发器。否则修改会报错
脚本：

 --删除表
 drop table students purge;
 drop table chengjs purge;
 --创建表
 create table students(xueh varchar2(5),xingm varchar2(10),xingb varchar2(3),nianl number(3));
 create table chengjs(xueh varchar2(5),kec varchar2(10),chengj number(3));
 --测试数据
 insert into students(xueh,xingm,xingb,nianl) values ('1','张三','男',18);
 insert into students(xueh,xingm,xingb,nianl) values ('2','李四','男',17);
 insert into students(xueh,xingm,xingb,nianl) values ('3','王五','男',19);
 insert into students(xueh,xingm,xingb,nianl) values ('4','马六','男',18);
 insert into chengjs(xueh,kec ,chengj) values ('1','语文',123);
 insert into chengjs(xueh,kec ,chengj) values ('1','数学',143);
 insert into chengjs(xueh,kec ,chengj) values ('1','英语',133);
 insert into chengjs(xueh,kec ,chengj) values ('2','语文',63);
 insert into chengjs(xueh,kec ,chengj) values ('3','英语',12);
 commit;
 --创建视图
 create or replace view stu_cheng_view
 as
 select s.xueh,s.xingm,c.kec,c.chengj
 from students s,chengjs c
 where s.xueh = c.xueh ;
 --命令行查看表结构：desc stu_cheng_view

如果在stu_cheng_view这个视图里面插入记录：

 insert into stu_cheng_view values('4','马六','数学',128);
   sql>ORA-01779: 无法修改与非键值保存表对应的列
   --因为视图是虚拟表，由两个表合成的，所以无法修改

但是如果想要修改，就必须使用instead of触发器

 create or replace trigger view_trigger
 instead of insert
 on stu_cheng_view
 for each row
 declare
 	cursor stu_cur is select * from students s where s.xueh = :new.xueh;
 	cursor cheng_cur is select * from chengjs c where c.xueh = :new.xueh and c.kec = :new.kec;
 	a stu_cur%rowtype;
 	b cheng_cur%rowtype;
 	begin
 	open stu_cur;
 	open cheng_cur;
 	fetch stu_cur into a;
    fetch cheng_cur into b; 
 	if stu_cur%notfound then
 	     insert into students(xueh,xingm,xingb,nianl) values (:new.xueh,:new.xingm,null,null);
 	else
 	     update students set xingm = :new.xingm where xueh = :new.xueh;
 	end if;
 	if cheng_cur%notfound then
 		  insert into chengjs(xueh,kec ,chengj) values (:new.xueh,:new.kec,:new.chengj);
 	else
 		    update chengjs set chengj = :new.chengj where xueh = :new.xueh and kec = :new.kec;
 	end if;
 	close stu_cur;
 	close cheng_cur;
 end;

DDL触发器（模式触发器）：
适用于我们在创建，删除，，等DDL操作时，记录下我们的操作；
语法：

CREATE [OR REPLACE] TRIGGER trigger name {BEFORE | AFTER } { DDL
event} ON {DATABASE | SCHEMA} 范围是on database、on schema ， on schema
作用范围只是在hr用户下create table等触发，其他用户则不会。若是on database则其他用户create table时会触发该触发器

案例：

--创建日志表
create table table_log(myuser varchar2(15),
mydate varchar2(20),
user_type varchar2(10),
obj_name varchar2(15),
obj_type varchar2(10) );
--创建触发器
create or replace trigger trr_log_table after drop or create on schema 
begin 
insert  into table_log values (user,sysdate,sys.dictionary_obj_name,sys.dictionary_obj_owner,sys.dictionary_obj_type);
end;

创建完成之后，在执行其他DDL操作，我们的操作记录就会留在table_log 这个表中