oracle 创建触发器

一.什么是触发器

触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

 

1.触发器的功能:

• 允许/限制对表的修改
• 强制数据一致性
• 自动生成派生列，比如自增字段
• 提供审计和日志记录
• 防止无效的事务处理
• 启用复杂的业务逻辑

二.创建触发器

1.基本语法结构

Create [or replace] trigger [模式.]触发器名 
before| after insert|delete|(update of 列名) 
On 表名 
[for each row] 
When 条件 
PL/SQL块 

•  触发器名：触发器对象的名称。
  　　         由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
• 触发时间：指明触发器何时执行，该值可取：
                 before:表示在数据库动作之前触发器执行
                 after:表示在数据库动作之后出发器执行
• 触发事件：指明哪些数据库动作会触发此触发器：                      
  　　         insert：数据库插入会触发此触发器;
                 update：数据库修改会触发此触发器;
  　　         delete：数据库删除会触发此触发器。
• 表名：数据库触发器所在的表。
• for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

举例

create trigger student_triger
before insert  or  delete or update of student_id
on TAN.student
for each row
begin   

if inserting then
   dbms_output.put_line('insert trigger is chufa le .....'); 
end if;

if updating then
   dbms_output.put_line('update trigger is chufa le .....'); 
end if;

if deleting then
   dbms_output.put_line('delete trigger is chufa le .....'); 
end if;

end;

  

•  创建名为student_triger的触发器

create trigger student_triger

 

• 在对表student 进行insert或者delete或者对student_id进行操作前,执行定义的语句  

before insert  or  delete or update of student_id
on TAN.student

•   对表student的每一行进行该操作

for each row

•  
  如果对表进行insert 操作,就输出"'insert trigger is chufa le ....."

if inserting then
   dbms_output.put_line('insert trigger is chufa le .....'); 
end if;

 

• 如果对表进行update操作,就输出"update trigger is chufa le ....."

if updating then
   dbms_output.put_line('update trigger is chufa le .....'); 
end if;

 

• 如果对表进行delete操作,就输出"delete trigger is chufa le ....."

if deleting then
   dbms_output.put_line('delete trigger is chufa le .....'); 
end if;

 

三.触发器的分类

1.DML触发器

   DML触发器是对表进行insert,update,delete操作时激发的,上面的例子就是典型的DML触发器

 

2.INSTEAD OF触发器
      在简单视图上往往可以执行INSERT、UPDATE和DELETE操作，但是在复杂视图上执行INSERT、 UPDATE和DELETE操作是有限的。如果视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询，那么将禁止在该视图上执行 DML操作。为了在这些复杂视图上执行操作，需要建立INSTEAD-OF触发器。

 

   INSTEAD OF 触发器的限制:

• INSTEAD OF触发器只适用于视图。
• INSTEAD OF触发器不能指定BEFORE和AFTER选项。
• 不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。
• INSTEAD OF触发器必须包含有FOR EACH ROW选项。

create or replace trigger tr_instead_of_stu_view
instead of insert on stu_view 
for each row
begin
     // sql 复杂的逻辑操作
end;

 

3.系统事件触发器

     oracle的系统事件触发器：系统事件触发器是指基于oracle系统事件（如logon和startup）所建立的触发器。通过这种触发器可以跟踪系统或数据库的变化。

 

首先建立LOG表
createtable triger_log_table(
username varchar2(20), 
log_time date, 
onoff varchar(6),
address varchar2(30));

其次建立触发器
createtrigger tr_logon
after logon ondatabase
begin
insertinto triger_log_table  values     (ora_login_user,sysdate,'logon',ora_client_i p_address);
end;
//当用户登录数据库后时,将有关信息写入创建的log表中

createtrigger tr_logoff
before logoff ondatabase
begin
insertinto triger_log_table  values(ora_login_user,sysdate,'logoff',ora_client_ ip_address);
end;
//当用户退出数据库时,将有关信息写入创建的log表中

 

 

• 在编写系统事件触发器时，应用开发人员经常需要使用事件属性函数。常用的事件属性函数如下：
• Ora_client_ip_address: 返回客户端的ip地址
• Ora_database_name: 返回当前数据库名
• Ora_des_encrypted_password: 返回des加密后的用户口令
• Ora_dict_obj_name :返回ddl操作所对应的数据库对象名
• Ora_dict_obj_name_list(name_list out ora_name_list_t): 返回在事件中被修改的对象名列表
• Ora_dict_obj_owner: 返回ddl操作所对应的对象的所有者名
• Ora_dict_obj_owner_list(owner_list out ora_name_list_t) :返回在事件中被修改的对象的所有者列表
• Ora_dict_obj_type: 返回ddl操作所对应的数据库对象的类型
• Ora_grantee(user_list out ora_name_list_t) :返回授权事件的授权者
• Ora_instance_num :返回例程号
• Ora_is_alter_column(column_name in varchar2) :检测特定列是否被修改
• Ora_is_creating_nested_table: 检测是否正在建立嵌套表
• Ora_is_drop_column(column_name in varchar2): 检测特定列是否被删除
• Ora_is_servererror(error_number): 检测是否返回了特定oracle错误
• Ora_login_user: 返回登录用户名
• Ora_sysevent: 返回触发器的系统事件名。