关于Oracle和PLSQL的学习记录10

------触发器--------
1.触发器是一种特殊类型的存储过程，类似于其他编程语言中的事件函数，当有操作影响到触发器保护的数据时，触发器就自动发生
触发器主要是通过事件进行触发而被执行的

2.创建语句触发器
语句触发器是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器，能够与insert、update、delete或者组合上进行关联
各语句触发器都只会针对指定语句激活一次
stu表和grade表
create or replace trigger tri_yj
before update on stu 
begin
  if updating then
    dbms_output.put_line('触发语句触发器');
  end if;
end;
触发器名为tri_yj，before update表示在修改数据之前触发
Oracle支持对insert、update和delete3中操作创建触发器：
create or replace trigger tri_yj
before insert or update or delete on stu 
begin
  if updating or inserting or deleting then
    dbms_output.put_line('触发语句触发器');
  end if;
end;
触发器的类型有数据插入、数据修改、数据删除3中，当有操作针对触发器表时，如果该表有相应操作类型的触发器，那么触发器就能自动引发执行

3.语句触发器被触发
set serveroutput on;
update stu set sage = sage + 1 where sage = 26;

4.查看触发器
Oracle提供了user_source视图和user_triggers视图存储有关触发器的信息
select trigger_name,trigger_type from user_triggers where table_name = 'stu';
select text from user_source where type = 'trigger' and name = 'tri_yj';

5.创建并触发行触发器
行触发器是指为受到影响的各个行激活的触发器，即每影响到一行记录就触发一次
create or replace trigger tri_hj
after update on stu
for each row
begin
  if updating then
    dbms_output.put_line('该行记录已更新');
  end if;
end;

update stu set sage = sage + 1 where sdept = '12计算机';
行触发器在begin语句前加上了for each row参数
用户在创建行触发器的同时要注意避免语句触发器也同样被触发，这可以通过编写触发器执行语句块来实现，也可以通过禁用语句触发器实现

6.insert触发器
create or replace trigger tri_in
after insert on stu
begin
  dbms_output.put_line('已插入一行数据至数据表stu');
end;
在创建触发器的时候要明确触发器的作用范围，且不要让触发器去完成Oracle后台已经能够完成的功能

7.update指定列触发器
create or replace trigger tr_up
before update of sno
on stu 
for each row
begin
  raise_application_error(-20001,'不能修改sno列的数据');
end;
为update指定列采用的是of关键字
raise_application_error表示自定义错误，其功能是输出错误信息
for each row语句表示行定操作的触发器为操作修改的每一行都调用一次

8.delete触发器
create or replace trigger tri_de
before delete on stu
for each row
begin
  raise_application_error(-20002,'不能删除stu表中的数据');
end;
如果要求不能删除某些指定的行，而非stu表的任意行，可以在begin..end语句块中加入if自己判断

9.创建instead of触发器
是在视图上而不是在数据基本表上定义的触发器，是用来替换所使用实际语句的触发器
创建一个视图：
create or replace view v_grade as
select stu.sno,stu.sname,stu.sage,grade.cname,grade.score fro stu inner join grade on stu.sno = grade.sno where stu.sdept = '计算机';

create or replace trigger tri_inof instead of insert on v_grade 
for each row
begin
  insert into stu(sno,sname,sage,sdept) values(:new.sno,:new.sname,new.sage,'12计算机');
  insert into grade(sno,cname,score) values(:new.sno,new.cname,new.score);
end;

使用instead of触发器，可以将所有视图都变成可更新的
基于多个基本表的视图必须使用instead of触发器来支持引用多个表中数据的插入、更新和删除操作；在视图上，每个insert、update或delete语句最多可以定义一个instead of触发器；
使用instead of触发器需要注意的4个事项：
只能被创建在视图上，并且该视图没有指定with check option选项
不能指定before或after选项
for each row子句是可选的，即instead of触发器只能在行级上触发，或只能是行级触发器，没有必要指定
没有必要在针对一个表的视图上创建instead of触发器

10.创建用户事件触发器
用户事件触发器是在用户事件上触发的触发器，用户事件一般包括：用户登录、注销、修改结构等，可以在create、alter、drop等DDL操作或数据库系统上被触发
create or replace trigger tri_yh
after ddl on schema
begin
  dbms_output.put_line('执行了ddl语句');
end;
用户事件触发器既可以建立在一个模式(schema)上，又可以建立在整个数据库(database)上

11.创建系统事件触发器
系统事件触发器是在系统事件上触发的触发器，系统事件一般包括数据库启动、关闭，用户的登录和登出，服务器错误等事件
create table logon_event(
  user_name varchar2(10),
  address varchar2(20),
  logon_date timestamp,
  logoff_date timestamp
);
create or replace trigger tri_xt
after logon on database
begin
  insert into logon_event(user_name,address,logon_date) values (ora_login_user,ora_client_ip_address,systimestamp);
end;

常用的事件函数(部分)：
ora_client_ip_address 返回客户端的ip地址
ora_database_name 返回当前数据库名
ora_des_encrypted_password 返回des加密后的用户口令
ora_dict_obj_name 返回ddl操作所对应的数据库对象名
ora_instance_num 返回例程号
ora_login_user 返回登陆用户名
ora_sysevent 返回触发器的系统事件名

Oracle一般提供5种事件用于触发系统事件触发器，且触发时间也限制了：
startup after 启动数据库实例之后触发
shutdown before 关闭数据库实例之前触发(非正常关闭不触发)
servererror after 数据库服务器发生错误之后触发
logon after 成功登录连接到数据库后触发
logoff before 开始断开数据库连接之前触发

要在数据库之上建立触发器，要求用户具有administer database trigger权限

12.禁用/启用触发器
alter trigger tri_yj disable;
set serveroutput on;
update stu set sage = sage +1 where sdept = '12计算机';
Oracle数据库中的触发器有两种状态，其状态名称和功能如下：
有效状态enable，当触发事件发生时，处于有效状态的数据库触发器trigger将被触发
无效状态disable，当触发事件发生时，处于无效状态的数据库触发器trigger将不会被触发，此时就相当于没有则会个数据库触发器

13.以表为单位禁用/启用触发器
alter table stu disable all trigger;
将disable改为enable即启用stu表上的所有触发器

14.将删除记录自动写入日志表
创建删除日志表：
create table del_tmp as select * from stu where 1=2;
创建触发器tri_del_tmp，该触发器的功能为插入用户刚刚删除的一行数据记录到del_tmp表中：
create or replace trigger tri_del_tmp
before delete on stu
for each row
begin
  insert into del_tmp values(:old.sno,:old.sname,:old.sgender,:old.sage,:old.sdept,:old.sbirth);
end;
触发器tri_del_tmp只能在删除操作执行前触发，因此其参数必须时before，否则将无法获取被删除数据

15.限制非工作时间操作数据
create or replace trigger tri_sjxz
before insert or delete or update
on stu
begin
  if(to_char(sysdate,'day') in ('星期六','星期日')) or (to_char(sysdate,'HH24:MI') not between '9:00' and '18:00') then
    raise_application_error(-2001,'不是上班时间，不能修改stu表');
  end if;
end;

raise_application_error是Oracle PLSQL种的一个自定义错误函数，其功能是将应用程序专有的错误从服务器端传达到客户端

16.限制对保护数据的操作
create or replace trigger tr_xzcz
before update of sage or delete on stu
for each row
when (old.sdept = '12计算机')
begin 
  case 
    when updating ('sage') then 
      if :new.sage <> :old.sage then 
        raise_application_error(-20001,'本班级学生年龄不能修改');
      end if;
    when deleting then
      raise_application_error(-20003,'不能删除本班级 的学生记录');
  end case;
end;

Oracle提供的谓词对应行为以及其取值：
inserting 如果触发语句是insert语句，则为true，否则为false
updating 如果触发语句为update，则为true，否则为false
deleting 如果触发语句为delete，则为true，否则为false

如果需要对表中的某个列的修改进行判定和获取，可以是哟个of关键字

17.实现级联更新
级联更新是指当主表中的某个值发生了改变，子表中的该值也要随着发生变化，不需要用户再手动去子表中调整
create or replace trigger tr_jigx
after update of sno on stu
for each row
begin
  dbms_output.put_line('旧的sno值是'||:old.sno||'、新的sno值是'||:new.sno);
  update grade set sno = :new.sno where sno = :old.sno;
end;

18.创建数据对象结构操作事件日志
创建操作事件日志表ddl_event：
create table ddl_event(
  crt_date timestamp primary key,
  event_name varchar2(20),
  user_name varchar2(10),
  obj_type varchar2(20),
  obj_name varchar2(20)
);
创建触发器：
create or replace trigger tr_ddl
after ddl on schema
begin
  insert into ddl_event values(systimestamp,ora_sysevent,ora_login_user,ora_dict_obj_type,ora_dict_obj_name);
end;
schema表示操作对象为模式而非数据库
触发器仅在被触发语句触发时进行集中的、全局的操作，与具体的用户和数据库应用无关

19.删除触发器
drop trigger tri_yj;
删除触发器必须具有一定的权限
触发器和某一指定的表有关，当该表被删除时，任何与该表有关的触发器同样会被删除