Oracle回顾--触发器

昨天看完了触发器，今天再来写写博客，这样，我又可以复习一下啦……挺好的……

触发器是建立在某个具体的表上的，且触发器在某些事件发生时，由Oracle自动执行，触发器的执行对应用来说是透明的。

一、事件主要有以下几种类型：

1、在指定表/视图中的DML操作：update,delete,insert

2、DDL操作：create ,alter,drop,用于审核和控制数据库操作

3、系统和用户数据库事件：

            用户的登录或注销

            数据库的打开或关闭

            特定的错误消息等

二、触发器的类型：

1、DML触发器（行级触发器、语句级触发器）

        语句级触发器不管执行的语句影响了几行，或者没有影响任何行，这个触发器都会在触发谓词为真时，触发一次该触发器。

        行级触发器执行的次数等于受DML操作影响的数据行数，若没有影响任何一行数据，则不触发。

2、DDL触发器

3、instead of 触发器：用于对多源表的视图进行DML操作，因为多源表的视图本身不能进行DML操作。

4、复合触发器

5、系统和用户事件触发器

可以为表创建before、after 类型的触发器，不能为视图创建before、after类的触发器，只有instead of类型的触发器可以建立在视图上。没有在select类型上的触发器。

三、创建DML触发器：

create  [ or replace ]  trigger 触发器名称

before | after | instead of                              --------------------------------触发类型，同时满足时用or分隔

delete | insert | update [of 列名]                  --------------------------------触发事件

on 表名                                                            --------------------------------作用对象

[for each row [when 条件 ]  ]                        --------------------------------带条件行级触发器的标识，，，用于对表进行修改时，保存修改前的数据，即保存历史数据。

begin

          PL/SQL语句；                                     --------------------------------触发器执行的操作

end;

eg：

create or replace trigger row _del_tr

after

delete

on customernew

for each row when(OLD.customer_name='ANNA')        ------------设置条件。OLD，表示在执行语句外，引用原值，条件必须用圆括号括起,当删除行的customer_name='ANNA‘时才触发触发器

begin

        dbms_output.put_line(’表customernew的1行已经被删除');

end;

Oracle中的触发器谓词有三种，INSERTING,UPDATING,DELETING.通过谓词，用户可以知道对表进行的DML操作的记录，谓词实际是一个布尔值，在触发器内部根据激活动作，会重新赋值。

四、行触发器的变量引用：

关联行：new 、old 分别表示触发器被激活时，当前行新数据和原数据。

new 表示修改后的行，通过new可以引用新行中的各列的值。如   new.cust_firtst_name,

old 表示修改前的行，通过old可以引用原来列的值，如   old.cust_first_name.

在触发器的可执行代码中，要通过old，new引用某列的值，在前面加上":",在insert命令上old没有意义，new表示新插入的行，在delete命令上，new没有意义,old表示被删除的一行。

-----------------------------将对表进行的DML操作，加入到表中，形成历史记录--------------------------------------------

create or replace trigger tr_ca_history

before

update or delete

on categories

for each row

begin

           insert into categories_history values(:old.category_name,:old.category_description,:old.category_id,sysdate);

end;

----------------------------------------------将插入值的某列转为大写-----------------------------------------------------------------

create or replace trigger tr_cus_upper

before

update or insert

on customernew

for each row

begin

         :new.status :=upper(:new.status);

end;

在触发器内部使用:new变量时，触发动作不能包括delete操作，创建行触发器为列赋值，通过:new更改列的值，即使update中未涉及到该列，数据表中该列的值也会被修改，不能修改old引用的值。

五、变量引用与referencing，

referencing主要用于为new ，old起别名

-----------------------取最大编号加1作为新插入数据的编号-------------------------------------

create or replace trigger tr_cus_insert

before

insert

on customernew

referencing new as new_value

for each row

begin

        declare

              max_id number;

        begin

              select max(customer_id) into max_id from customernew;

              :new_value :=max_id+1;

        end;

end;

六、instead of 触发器

instead of 触发器定义在视图上，用来替换实际的操作语句，该类型的触发开阔地可以帮助用户实现更新视图所属的源表，语法与之前的一样，"instead of “ 的意思是当在视图上进行DML操作时，不去执行指定的是DML语句，而是执行触妇器的代码。

视图上的触发器都是行触发器，因此，在建触发器时for each row 可以省略，受影响的每一行数据都会激发触发器一次，可以用关联行old,new

eg:

create or replace trigger tr_cus_view

instead of

delete or update

on view_cus            -------------视图上的触发器都是行触发器，因此，在建触发器时，for each row可以省略，受影响的每一行数据都会激发触发器一次，可以用关联行old,new

for each row

begin

         if deleting then

               if :old.status = 'gold' then

                     raise_application_error(-20001,'gold顾客不能删除');                ---------------------------给出错误提示

              end if;

        end if;

        if updating then

            if :new.credit_limit <= :old.credit_limit then

                  raise_application_error(-20002,'赊销限额不能减少');

             end if;

         end if;

end;

-------------------------在有多个数据源的视图中，执行update或delete操作是不允许的，可以利用触发器转化另外的SQL语句执行达到效果。------------------------------------

-------------insert into vw_order_cus values(2459,sysdate,117,'guillaume');-------------视图源于customernew表和orders表

create or replace trigger tr_view

instead of

update or delete or insert

on vw_order_cus

for each row

begin

            if inserting then

                      insert into orders(order_id,order_date,customer_id) values(:new.order_id,:new.order_date,:new.customer_id);

            end if;

            if deleting then

                      delete from orders where order_id = :old.order_id;

            end if;

end;

在视图上进行DML操作还要遵守基表上的完整性约束。

七、DDL触发器

sysevent取到当前操作的类型。

if sysevent = 'drop ' then      -------- 如果事件类型为drop

       if dictionary_obj_name = 'cus_log' then    -------------数据字典对象名

on schema

八、系统事件触发器

oracle中的系统事件：

       startup                            数据库打开后触发，模式下不可以

       shutdown                       数据库关闭前触发，模式下不可以

       logon                              客户程序登陆后触发

       logoff                              客户程序登录前触发

       servererror                     错误消息出现后触发

create or replace trigger tr_db_log

before

shutdown

on database

begin

              insert into database_log values(user,'shutdown',sysdate);               ----------------------user,当前用户

end;

九，

一个用户可以拥有数据库中的多个对象，用户事件触发器的作用对象不是单个对象，而是用户所拥有的所有对象的集合，即常说的schema，以下逡的before，after时机均可创触发器：

alter:修改对象属性

analyze:分析数据瑶的统计信息，以供优化器使用

associate statistics:关联统计信息

audit：开启对象或系统上的审计功能，以便记录和跟踪用户操作

comment:为表或列添加注释，这些注释可以通过数据字典获取，

create:创建对象

disassociate statistics:取消统计信息的关联

drop:删除对象

grant:为数据库用户授予权限或角色

noaudit:关闭对象或系统上的审计功能

rename:重命名数据库中的对象

revoke:收回数据库用户的权限或角色

truncate:删除表中所有的记录，并且不能回滚

以下事件只有after触发器：

suspend：当程序运行出现错误，并导致程序挂起。

create or replace trigger tr_truncate_table

after

truncate

on system.schema              --------------作用对象为用户system的所有对象

begin

            insert into truncate_log values(ora_dic_obj_name,user,sysdate);     ----------------ora_dic_obj_name 被删除对象的对象名称（被删除记录的表名），user，当前用户

end;

十、与触发器相关的视图（数据字典）

user_triggers:存储当前用户所拥有的触发器

dba_triggers:存储管理员所拥有的触发器

all_triggers:存储所有的触发器

user_objects:存储当前用户所拥有的对象，包括触发器

dba_objects:存储管理员所拥有的对象，包括触发器

all_objects:存储数据库中所有的对象，包括触发器

select trigger_name（触发器名）,trigger_type（触发器类型，before/after....）,triggering_event（触发事件）,table_name（表名）,trigger_body（执行块）,status（状态） from user_triggers where lower(trigger_name)='del_tri'；

十一、启用/禁用触发器

alter trigger 触发器名 enable/disable