oracle触发器

类型

行级触发器:

• FOR EACH ROW
  影响的每一行都会执行触发器

语句级出发器

• 默认的模式,一句话才执行一次触发器
  触发器不能嵌套,不能含有事务控制语句

何时触发

• Before:条件运行前
• After:条件运行后
• Instead of:替代触发，作用在视图上

创建触发器

实例一

1.创建一个与scott下的emp表结构一样的表emp_his，并不添加数据

create table emp_his as select * from employees where 0=1;

这种构造与现存表相同结构的表，是不会将comment带过来的

2.然后在emp表中创建before delete类型的触发器，
当即将删除emp表记录时，将相应记录插入到emp_his中。

create or replace trigger hr.e_delete
    before delete on employees
    for each row
    begin
    insert into emp_his values(:old.employee_id,
        :old.first_name,:old.last_name,:old.email,
        :old.phone_number,:old.hire_date,:old.job_id,
        :old.salary,:old.commission_pct,:old.manager_id,
        :old.department_id);
    end e_delete;

实例二

禁止对表emp的salary进行更改

create or replace trigger e_update
before update of salary on emp
begin
if updating then
raise_application_error(-20001,'工资不能被改动');
end if;
end;

检验

hr@ORCL> update emp
  2  set salary=100
  3  where employee_id<100;
update emp
       *
第 1 行出现错误:
ORA-20001: 工资不能被改动
ORA-06512: 在 "HR.E_UPDATE", line 3
ORA-04088: 触发器 'HR.E_UPDATE' 执行过程中出错


hr@ORCL>

实例三

跟踪记录修改

1.创建一个记录修改表t1用于存放修改的记录

hr@ORCL> create table t1 as select salary old,salary new from emp where 1=0;

表已创建。

hr@ORCL> desc t1;
 名称
                                                                                                                                       是否为空? 类型
 ---------------------------------------------------------------------------------------------------------------------
 OLD

NUMBER(8,2)
 NEW

NUMBER(8,2)

hr@ORCL>

2.创建行级触发器,记录对emp的salary字段的修改

create or replace trigger tr_1
before insert or update of salary on emp
for each row
begin
insert into t1 values(:old.salary,:new.salary);
end;

3.检验

hr@ORCL> update emp
  2  set salary =100
  3  where employee_id=107;

已更新 1 行。

hr@ORCL> select * from t1;

       OLD        NEW
---------- ----------
      4200        100

hr@ORCL>

实例四

创建基于多表的视图的触发器
1.创建表t1,t2,视图v1

create table t1 as select salary old,salary new from emp where 1=0;

create table t2 as select new new,old older from t1;

create view v1 as select old,t1.new,older from t1,t2 where t1.new=t2.new;

２.创建触发器

create or replace trigger tr_v
instead of 
insert or update or delete on v1
for each row
begin
if inserting then
    insert into t1 values(:new.old,:new.new);
    insert into t2 values(:new.new,:new.older);
end if;
end;

如果有报触发器有编译错误,可以使用show error命令

3.测试

触发器已创建

hr@ORCL> select * from v1;

       OLD        NEW      OLDER
---------- ---------- ----------
      4200        100       4200
         6          7          6

hr@ORCL> insert into v1 values(1,2,3);

已创建 1 行。

hr@ORCL> select * from v1;

       OLD        NEW      OLDER
---------- ---------- ----------
      4200        100       4200
         6          7          6
         1          2          3

hr@ORCL> select * from t1;

       OLD        NEW
---------- ----------
      4200        100
         6          7
         1          2

hr@ORCL> select * from t2;

       NEW      OLDER
---------- ----------
       100       4200
         7          6
         2          3

hr@ORCL>

实例五

创建一个登录审计触发器
1.创建用来记录登录信息的表

create table login_table(user_id varchar2(15),log_date date,action varchar2(15));

2.创建触发器

CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO login_table(user_id, log_date, action)
VALUES (USER, SYSDATE, 'Logging on');
END;

3.测试

sys@ORCL> conn hr/hr
已连接。
hr@ORCL> select* from login_table;

USER_ID                        LOG_DATE       ACTION
------------------------------ -------------- ------------------------------
HR                             12-12月-16     Logging on
HR                             12-12月-16     Logging on

hr@ORCL>

触发器管理

禁用触发器

alter trigger e_delete disable;

禁用某个表上所有的触发器

alter table emp disable all triggers;

查all_trigger表得到触发器名字

select trigger_name,trigger_type,table_owner,table_name from all_triggers;

查看触发器内容

select text from all_source where type='TRIGGER' and name='E_DELETE';

注意,平时在写sql的时候不注意大小写,但是在此处涉及到触发器名字的时候一定要大写

hr@ORCL> select text from all_source where type='TRIGGER' and name='E_DELETE';

TEXT
---------------------------------------------------------------------------------------------------------------------
trigger hr.e_delete
before delete on employees
for each row
begin
insert into emp_his values(:old.employee_id,
:old.first_name,:old.last_name,:old.email,
:old.phone_number,:old.hire_date,:old.job_id,
:old.salary,:old.commission_pct,:old.manager_id,
:old.department_id);

end e_delete;

已选择11行。

hr@ORCL>

删除触发器

drop trigger e_delete_1;