Oracle 触发器

最新推荐文章于 2022-12-03 10:40:09 发布
zerolsy
最新推荐文章于 2022-12-03 10:40:09 发布
阅读量1.2k
点赞数
文章标签： oracle 数据库 database object schema delete
本文链接：https://blog.csdn.net/zerolsy/article/details/2908962
版权
 
 触发器:是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。 
1 能让触发器执行的操作事件
(1)DML事件：如：insert、delete、update。
(2)系统事件：如：关闭数据库shutdown、启动数据库startup、服务器出错servererror发生时。
(3)用户事件：如注册连接logon或注销logoff。
(4)DDL事件：如create、drop、alter发生时。
2 触发器功能:
(1)审计：提供审计和日志记录。
(2)实现复杂业务规则。
(3)产生导出值。
(4)远程数据复制。
(5)可进行系统事件处理。
(6)强制数据一致性
3 触发器类型
(1)DML触发器:它是在执行insert、delete、update语句时被激发执行。根据触发器所依赖的表对象不同，可将DML
触发器进行分为行级row触发器和语句级statement触发器。
   使用最多，它只能定义在表上。
(2)替代触发器：是被激发用于代替执行DML语句，它可以定义在表上、视图上(主要用于对相关视图的更新)。
(3)系统触发器：可在两种不同的事件(DDL和数据库)上激活。
DDL事件包括：create alter drop语句，
数据库事件：服务器的启动或关闭、用户的登录或退出，以及服务器错误。系统触发器常用于数据库的管理和维护。
触发器定义通用格式:
    create or replace trigger <trigger_name>
    [before | after | instead of]<triggering_event>
    [referencing<referencing_clause>]
    [when<trigger_restriction>]
    [for each row]
    <trigger_body>;
其中：
◇<triggering_event>：指激发触发器的特定事件，它可是下列事件中的一个或多个，若多个事件则用or分离。
delete <event_ref>;
insert <event_ref>;
update <event_ref>;
update of column,column...<event_ref>;
<ddl_statement>on[schema.]{table|view}
<ddl_statement>on[database]|[schema.]schema};
servererror;
logon;
logoff;
startup;
shutdown;
◇<event_ref>:为激发触发器执行的对象。包括表、视图和嵌套表列。
on [schema.]<table_name>
on [schema.]<view_name>
on [nested table<nested_table_column>of][schema.][view_name]
◇<referencing_clause>为：
referencing old [as] old [for each row]
referencing new [as] new [for each row]
referencing parent [as] parent [for each row]
◇<referencing>:为当前:new和:old或:parent指定一个不同名称，用于引用正处于修改状态下行中的数据。这样可
进行保存或比较新旧值记录的值。只能用于行触发器。
◇<trigger_body>:触发器主体。
◇when <trigger_resrtiction>:指在满足了条件<trigger_resrtiction>后，才触发。
◇<trigger_name>触发器名称。
4 DML触发器:
在实际应用中，DML触发器最为常用。DML语句格式：
    create or replace trigger <trigger_name>
    [before | after]<triggering_event>
    [referencing<referencing_clause>]
    [when<trigger_restriction>]
    [for each row]
    <trigger_body>;
(1)触发时机
在DML触发器中，根据其触发时机不同，触发时可分为：before和after。因此可分为before触发器和after触发器。
(2)行级触发器和语句触发器
当创建触发器的语句添加了子句for each row,则为行级触发器。否则为语句触发器(系统默认)。
行级触发器：是由触发语句所变更的每一行激发的，即每变更一行就触发一次。
语句级触发器：就是语句执行之前或之后触发一次。
5 DML行级触发器属性
        不同事件中标识符:old和:new的对应值
------------------------------------------------------------------------
  触发事件      标识符：old     标识符：new 
------------------------------------------------------------------------
  insert        无定义(所有字段为空) 该语句结束时将要插入的值
  update        更新前，行的原始值。  该语句结束时将要更新的值
  delete        删除前，行的原始值。  无定义(所有字段为空)
-------------------------------------------------------------------------   
DML触发器还有另外三个逻辑属性，也称为谓词表达式。用来确定是何种DML操作语句触发了触发器的执行。
 ----------------------------------------------------------------------------------------
┆       ┆               谓词表达式(属性)           ┆   
┆执行的DML语句   ┆-----------------------------------------------------------------------
┆       ┆   inserting   ┆   updating    ┆   deleteing   ┆
-----------------------------------------------------------------------------------------
┆   insert  ┆   true        ┆   false       ┆   false       ┆   
┆       ┆-----------------------------------------------------------------------
┆   update  ┆   false       ┆   true        ┆   false       ┆
┆        ------------------------------------------------------------------------
┆   delete  ┆   false       ┆   false       ┆   true        ┆
 ----------------------------------------------------------------------------------------
◇触发器谓词判断语句的类型
create or replace trigger stuInfo_infor
  before insert or delete or update on stuInfo  
  for each row
declare
begin
   if inserting then
      insert into stuMark values('s271820',:new.stuNo,0,0);
   end if;
   if deleting  then
      delete from stuMark where stuNo=:old.stuNo;
   end if;   
   if updating then
      update stuMark set writtenExam=100,labExam=100 where stuNo=:old.stuNo;
   end if;
end stuMark_infor;
◇利用referencing子句来引用触发器语句体中的数据
命令格式：
  referencing [old as <custom_name_for_old>][new as <custom_name_for_new>]
其中，<custom_name_for_old>和<custom_name_for_new>是分别为:old和:new定义的名称。
referencing子句为当前:new和:old指定一个不同名称,用于引用正处于修改状态下行中的数据。这样可以保存或比较旧记录的值。
该子句用在触发事件之后且when子句之前使用。只能用于行级触发器。
◇when条件子句的使用
只能用于行级触发器。表示满足条件时才触发。
◇利用:new产生导出数据
6替代触发器
替代触发器(instead of)触发器可用来操纵对视图的插入、修改和删除。当一个视图是根据几个表创建的时候，
替代触发器是非常有用的。一旦对视图进行了DML操作，则该操作会触发视图的替代触发器，改由触发器语句体
中的DML语句对构成该视图的基本表进行操作。其中视图中不能包括以下命令参数：
集合操作：如交、差、并。
聚集操作：如sum、avg等。
group by、connect by 或start with以及distinct子句。
before、after不能用于instead of操作。
替代触发器(instead of)语句格式：
    create or replace trigger <trigger_name>
    [instead of]<triggering_event>
    [referencing<referencing_clause>]
    [when<trigger_restriction>]
    [for each row]
    <trigger_body>;
建立视图：
create or replace view stuInfo as select * from stuInfo;
建立触发器：
在进行更新时,检查stuName的新旧值，如果不同，则进行更新。
create or replace trigger stuInfo_change
  instead of update on v_stuinfo  
  for each row
declare
  -- local variables here
begin
  if(:new.stuName<>:old.stuName) or (:old.stuName is null and :new.stuName is not null) then
    update stuInfo set stuName=:new.stuName where stuNo=:new.stuNo;
  end if;
end stuInfo_change;
update v_stuInfo set stuName='test' where stuNo='s25303';
7 系统触发器
根据事件类型系统触发器分为:
数据库触发器：数据库触发器的DDL事件包括：create alter drop事件。
模式触发器：服务器的启动或关闭以及服务器错误。
用户触发器：用户登录或退出。
系统触发器语法格式：
    create or replace trigger [schema.] <trigger_name>
    [before | after]<triggering_event>
    [<ddl_event_list><database_event_list>]
    on{database | [schema.]schema}
    [when_clause]
    <trigger_body>;
其中：
[before | after]：为触发时机。
<ddl_event_list>：是一个或多个DDL事件。
<database_event_list>：是一个或多个数据库事件。若定义中使用了on database子句，则为
数据库级触发器，如果选用了on schema子句，则为模式触发器。
(1)创建数据库登录触发器，触发时机为after.
创建系统信息表
create table connection_audit(
 login_date date,
 username varchar2(30));
create or replace trigger logon_audit
  after logon on database  
begin
  insert into connection_audit(login_date,username ) values(sysdate,user);
end logon_audit;
(2)创建用于记录登录失败信息的触发器
create or replace trigger logon_failures
  after servererror on database  
begin
  if(is_servererror(1017)) then
    insert into connection_audit(login_date,username ) values(sysdate,'ora-1017'); 
  end if;
end logon_failures;
(2)数据库审计
数据库审计，可以使用startup触发器和shutdown触发器来记录数据库启动和关闭的时间，用户等信息。
create table updown_log(
 database_name varchar2(30),
 event_name varchar2(20),
 event_time date,
 triggered_user varchar2(30));
create or replace trigger log_startup_infor
  after startup on  database
begin
  insert into updown_log(database_name,event_name,event_time,triggered_user)
  values(sys.database_name,sys.sysevent,sysdate,sys.login_user);
end log_startup_infor;
create or replace trigger log_shutdown_infor
  before shutdown on database  
begin
    insert into updown_log(database_name,event_name,event_time,triggered_user)
    values(sys.database_name,sys.sysevent,sysdate,sys.login_user);
    commit;
end log_shutdown_infor;
(3)模式或DDL事件触发器
审计模式修改的触发器,关键词是schema.
create table alter_ddl_audit(
 object_owner     varchar2(30),
 object_name      varchar2(30),
 object_type      varchar2(20),
 object_by_user   varchar2(30),
 alteration_time  date);
create or replace trigger audit_schema_alter
  after create or alter or drop on system.schema  
begin
  insert into alter_ddl_audit(object_owner,object_name,object_type,object_by_user,alteration_time)
  values(sys.dictionary_obj_owner,sys.dictionary_obj_name,sys.dictionary_obj_type,sys.login_user,sysdate);
end audit_schema_alter;
8 触发器的状态
触发器的状态包括无效(disable)与有效(enable)两种状态。
alter trigger <trigger_name>[disable | enable]
9触发器应用
(1)触发器实现字段列自增长
create table stu(
 stuID number(10),
 stuName varchar2(20));
create sequence autoId start with 1 increment by 1 cache 200;
create or replace trigger getautoId
  before insert on stu  
  for each row
declare
  -- local variables here
begin
  select autoId.Nextval into :new.stuId from dual;
end getautoId;
insert into stu values(null,'s');
insert into stu values(null,'s1');
insert into stu values(null,'s2');
insert into stu values(null,'s3');
 