昨天看完了触发器,今天再来写写博客,这样,我又可以复习一下啦……挺好的……
触发器是建立在某个具体的表上的,且触发器在某些事件发生时,由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