Oracle学习1213触发器

一、理解 oracle 11g 中的触发器的种类

1，触发器：是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的 DML 语句和 DDL 语句，触发器不能被显式调用

2，触发器的功能：自动生成数据，自定义复杂的安全权限，提供审计和日志记录，启用复杂的业务逻辑

3，创建触发器的语法：

CREATE [OR REPLACE] TRIGGER trigger_name

AFTER | BEFORE | INSTEAD OF  --after 和 before 是作用于表，instead 是作用于视图

[INSERT] [[OR] UPDATE [OF column_list]]  --表示当有哪些操作时会引起触发器的执行

[[OR] DELETE]

ON table_or_view_name  --表示在哪个表或者视图上

[REFERENCING{OLD [AS] old / NEW [AS] new}]

[FOR EACH ROW]  --如果加上表示是行级触发器，否则就是表级触发器

[WHEN (condition)]

pl/sql_block;

注意：触发器中不能使用 rollback，commit，create，drop，alter，saveopint 等；例：

当用户插入记录到 studeht 时，不能插入 sno 是负值的记录，insert into student values(-6, 'A', 26);

create or replace trigger trig2 before insert on student for each row

begin

if :new.sno < 0 then  -- :new 表示的是用户刚刚执行的 DML 语句

raise_application_error(-20001, '学号错误，不能插入表中');  --表示如果是负数就抛出异常，阻止 DML 请语句的执行

end if;

end;

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

当执行 insert 的时候，:new 存在，:old 不存在；当执行 delete 的时候，:new 不存在，:old 存在；当执行 update 的时候，:new 存在，:old 存在，是先把原有的记录删除，再把修改后的记录加到表里去；

4，例：当用户插入记录到 student 时，如果插入的 sno 是负值的，那么，把 sno 变成相反的正数再进行插入

insert into student values(-6, 'A' ,20); 当执行些操作时插入操作是 insert into student values(6, 'A', 20);

create or replace trigger trig2 before insert on student for each row

begin

if :new.sno <0 then

:new.sno := -:new.sno;  --如果值是负数，改变值为负负得正的数

end if;

end;

5，触发器类型，如下图：

5-1，对于 INSTEAD OF 触发器的使用，例：

创建一个视图，其结果为 student 表和 address 表依据各自的 id 列连接成的表；如果要执行以下语句，修改的是非键保留表的值

update view_stu_add set zz='安阳' where sname =‘Kite';  --这条语句单独执行时会报错，这时就可以用到 instead of 触发器

create or replace trigger tri_view instead of update on view_stu_add for each row

declare

aa number :=0;

begin

select sno into aa from student where sname = :old.sname;

delete address where sno = aa;

insert into address values(aa, :new.zz);

end;

6，对 DML 触发器的进一步使用：当用户对 成绩表 进行 增删改 的时候，把当时的情况输出

create or replace trigger trig1 before insert or update or delete on 成绩表 for each row

begin

if inserting then   --如果执行插入操作时

dbms_output.put_line('插入的学生的学号是：’ || ：new.code || '，姓名是' || :new.name);

end if;

if updating then  --如果执行修改操作时

dbms_output.put_line('原始记录的学生的学号是：' || :old.code || '，姓名是' || :old.name || '，新的学生的学号是：' || :new.code || '，姓名是：' || :new.name);

end if;

if deleting then  --如果执行删除操作时

dbms_output.put_line('删除记录的学生的学号是：' || :old.code || '姓名是：' || :old:name);

end if;

end;

二、掌握 oracle 11g 中使用触发器实现复杂完整性的方法

三、理解 SQLServer 和 oracle 11g 的触发器语法上的差异

四、掌握 oracle 11g 中的 INSTEAD OF 触发器的建立方法

五、掌握 oracle 11g 中的特殊类型触发器的建立方法，即：模式触发器、数据库启动关闭触发器、用户登录退出触发器

1，模式触发器：例：在当前模式下执行删除对象的操作时，将删除的信息存储到一个表中

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

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;

2，常用系统变量

2-1，Ora_client_ip_address 返回客户端的 ip 地址；

2-2，Ora_database_name 返回当前数据库名；

2-3，Ora_login_user 返回登录用户名；

2-4，Ora_dict_obj_name 返回 ddl 操作所对应的数据库对象名；

2-5，Ora_dict_obj_type 返回 ddl 操作所对应的数据库对象的类型；

2-6，Ora_sysevent 返回数据库登录和退出的信息，也就是 shutdown 和  shutup

3，数据库启动关闭触发器：要用 sys 用户创建此触发器

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

数据库启动时触发：

create or replace trigger tr_shutup after startup on database

begin

insert into event_table values(ora_sysevent,sysdate);

end;

数据库关闭时触发：

create or replace trigger tr_shutdown before startdown on database

begin

insert into event_table values(ora_sysevent,sysdate);

end;

4，用户登录退出触发器：必须要使用 system 及以上权限用户才可以创建

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

用户登录时：

create or replace trigger tr_login after logon on database

begin

insert into log_table(username, logon_time, address) values(ora_login_user, sysdate, ora_client_ip_address);

end;

用户退出时：

create 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;

5，启用和禁用触发器

5-1，禁用：ALTER TRIGGER aiu_itemfile DISADLE;

5-2，启用：ALTER TRIGGER aiu_itemfile ENABLE;

6，删除触发器：DROP TRIGGER aiu_itemfile;

7，查看触发器：USER_TRIGGERS 数据字典视图包含有关触发器的信息；

SELECT TRIGGER_NAME FROM USER_TRIGGERS WHERE TABLE_NAME='EMP';

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

一般就用：select * from user_triggers; 查询当前用户下的所有触发器信息；