第22部分 oracle11g的触发器

一、目标

1、理解oracle11g中的触发器的种类

2、掌握oracle11g中使用触发器实现复杂完整性的方法

3、理解SqlServer和oracle11g的触发器语法上的差异

4、掌握oracle11g中的INSTEAD OF触发器的建立方法

5、掌握oracle11g中的特殊类型触发器的建立方法，

即：模式触发器、数据库启动关闭触发器、用户登录退出触发器

二、触发器

1、触发器是当特定事件出现时自动执行的存储过程

2、特定事件可以是执行更新的DML语句和DDL语句

DML（增、删、改，不包含查）、DDL（创建、删除、修改）

3、触发器不能被显式调用

4、触发器的功能：

（1）自动生成数据

（2）自定义复杂的安全权限

（3）提供审计和日志记录

（4）启用复杂的业务逻辑

三、创建触发器语法

1、可以在表上建立after、before触发器【SQLserver只能建立after触发器】

2、可以在视图上建立instead of触发器

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]                                 
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]   #--deleted/inserted
[FOR EACH ROW]                                #--行级，表级
[WHEN (condition)]                            #触发条件，如果不写此触发条件，上述只要有insert,update或delete都会执行程序段
pl/sql_block;                                 #条件触发后，执行的程序段

四、举例

1、当用户插入、更新成绩表中的记录时，就输出提示“触发器响应了”

create or replace trigger trig1 before insert or update on student
begin
dbms_output.put_line('trig1 is interupt');
end;
/

2、插入一行

3、更新5行
也是触发一次触发器

4、更新n行，触发n次，需要在触发器中加入for each row

create or replace trigger trig1 before insert or update on student for each row
begin dbms_output.put_line('trig1 is interupt'); 
end;
/

有了for each row 就是行级触发器；

没有for each row 就是表级触发器；

五、AFTER、BEFORE触发器原理

六、不能用在触发器中的语句：

rollback、commit、create、drop、alter、savepoint等，不能用在触发器中

七、使用:new,触发器的声明部分必须要加for each row,都是针对当前行的操作

如果在触发器的plsql内使用:new或:old，就必须是行级触发器，必须要有for each row

1、当执行insert时(for each row)，:new存在，:old不存在

2、当执行delete时(for each row)，:new不存在，:old存在

3、当执行update时(for each row)，先把要改变的值进行delete，再把新的值insert，所以:new存在，:old也存在

4、当使用before时，可以用:new，不可以用:old

5、当使用after时，不可以用:new，可以用:old

6、:old的行是不能修改的，:old.sno:=:old.sno+1;

7、:new的行是可以修改的，:new.sno:=:new.sno+1;

八、触发器类型1

九、触发器类型2

1、DDL 触发器：——————在模式中执行 DDL 语句时执行

2、数据库级触发器： ————在发生打开、关闭、登录和退出数据库等系统事件时执行

3、DML 触发器：——————在对表或视图执行DML语句时执行

    （1）、语句级触发器：—————无论受影响的行数是多少，都只执行一次;（没有for each row语句，在begin代码段中不可以使用:new和:old。)

    （2）、行级触发器：——————对DML语句修改的每个行执行一次;（有for each row语句，在begin代码段中可以使用:new和:old）

    （3）、INSTEAD OF 触发器：—用于用户不能直接使用 DML 语句修改的视图[非键值保留表]

   （4）、before/after触发器

记录日志的触发方式：

常用系统变量

Ora_client_ip_address 返回客户端的ip地址

Ora_database_name 返回当前数据库名

Ora_login_user 返回登录用户名

Ora_log

Ora_dict_obj_name 返回ddl操作所对应的数据库对象名

Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型

常用系统变量

十、触发器类型——其他类型之模式触发器

1、创建表（用于存放drop的记录）

CREATE TABLE dropped_obj (obj_name VARCHAR2(30),obj_type VARCHAR2(20),drop_date DATE);

2、创建模式触发器（drop掉一个表后，才触发写入日期到dropped_obj表中）

CREATE OR REPLACE TRIGGER log_drop_obj AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj   VALUES( ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE, SYSDATE);
END;

十一、触发器类型——其他类型之数据库启动触发器

1、数据库启动触发器

（1）创建表

create table event_table ( event varchar2(30), time  date);

（2）创建启动触发器

create or replace trigger tr_startup 
after startup on database
begin
   insert into  event_table values(ora_sysevent,sysdate);
end;

2、数据库关闭触发器

（1）创建表

create table event_table ( event varchar2(30), time  date);

（2）创建关闭触发器

create or replace trigger tr_shutdown before shutdown on database
begin
   insert into  event_table values( ora_sysevent, sysdate);
end;

十二、触发器类型——其他类型之用户登录触发器

1、用户登录触发器

（1）、创建表记录登录日志

create table log_table(username  varchar2(20), logon_time  date, logoff_time  date, address varchar2(20));2、

（2）、创建用户登录触发器

create or replace trigger tr_logon after logon on database
begin
   insert into log_table(username,logon_time, address ) values(ora_login_user,  sysdate,ora_client_ip_address);
end;

2、用户退出触发器

reate or replace trigger tr_logoff before logoff on database
begin
   insert into log_table(username,logoff_time, address ) values(ora_login_user,  sysdate,ora_client_ip_address);
end;

十三、启用、禁用和删除触发器

1、启用和禁用触发器

SQL> ALTER TRIGGER aiu_itemfile DISABLE;

SQL> ALTER TRIGGER aiu_itemfile ENABLE;

2、删除触发器

SQL> DROP TRIGGER aiu_itemfile;

十四、查看有关触发器的信息

1、USER_TRIGGERS 数据字典视图包含有关触发器的信息

SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME='EMP';

SQL> SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';