Oracle笔记之Oracle触发器

Oracle触发器

概述
本篇博文中主要探讨以下内容：
为什么要使用触发器？

1. DML触发器(insert、update、delete等操作之前或者之后触发的事件)
2. 系统事件触发器(系统事件和用户事件)
3. 替代触发器(instead of)
4. 设置触发器的开关（开启和禁用）

一个典型的问题 ，在Oracle中怎样实现自增字段？

Oracle中可没有AUTO_INCREMENT关键字，只有MySQL中才有AUTO_INCREMENT，那么在Oracle中怎么实现id字段自增呢？
这就要用到我们提到的触发器了
但是触发器种类比较多，主要分为以下几种

（一）DML触发器

DML（insert、update、delete等）操作时的触发器

触发时机(before/after)

触发事件(insert ,update ,delete 或者是这三者中的组合)

触发类型（语句级还是行级）

触发条件（当条件为真时才会触发）

触发操作

触发顺序
 
     before 语句触发器
        

     before 行触发器
     
     after  行触发器

     before 行触发器
     
     after  行触发器


     after  语句触发器      

语句触发器和行级触发器的区别

语句触发器：一次只执行一次
行级触发器：一次可能执行多次（多行记录）   

 [declare]
    

    begin

    [exception]

    end;

(1)语句触发器

create or replace trigger tr_sec_emp before insert or update or delete on emp
begin
  if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SAT','SUN') then
     raise_application_error(-20000,'不能在休息日更改员工信息');
  end if;
end;
--使用条件谓词:当有多个触发事件时，如：insert ,update,delete时为了在触发器代码中
--区分具体的触发事件，可以使用 inserting,updating,deleting 
create or replace trigger tr_sec_emp before insert or update or delete on emp
begin
  case 
       when inserting then
         dbms_output.put_line('inserting...');
       when deleting then
         dbms_output.put_line('deleting...');
       when updating then
         dbms_output.put_line('updating');
  end case; 
end; 

(2)行级触发器

--创建一个before行级触发器
create or replace trigger tr_sec_emp before update of sal on emp for each row 
begin 
    dbms_output.put_line('Name: '|| :old.ename || ' Old Sal: '  || :old.sal || ' New Sal: ' || :new.sal);
end;
--限制行级触发器（一个小知识点：有两个关键字 :old和:new 新值和旧值）
create or replace trigger tr_sec_emp after update of sal on emp for each row
   when (old.deptno in (20,30))--注意这儿不能用:old
begin
   dbms_output.put_line('DeptNo: ' || :old.deptno || ' Name: '  || :old.ename || ' Old Sal: '  || :old.sal || ' New Sal: '|| :new.sal);
end;

(3)DML触发器的作用

1.实现数据的安全性(比如更改时间的限制)
2.实现审计功能:可以审计数据的变化

--审计每个人的sal变化情况
--创建个emp_sal_change表
create table emp_sal_change(ename varchar2(30),old_sal number,new_sal number,change_date date default sysdate);
--创建DML触发器
create or replace trigger tr_audit_sal_change after insert or  update of sal on emp for each row
begin
  if inserting then
    insert into emp_sal_change values(:new.ename,null,:new.sal,sysdate);
  else
    insert into emp_sal_change values(:old.ename,:old.sal,:new.sal,sysdate);
  end if;
end;

3.–实现数据的完整性(包括参照完整性)

--工资只能增不能减
create or replace trigger tr_check_emp before update of sal on emp for each row
begin
  if :new.sal < :old.sal then
    raise_application_error(-20001,'工资不能负增长');
  end if;
end;

（二）系统事件触发器

--startup和shutdown(关机和开机)
create or replace trigger tr_db_sstartup
after startup
on database
begin
 insert into DB_Log values(sysdate);
end tr_db_startup;

（三）替代触发器

--替代触发器,替代某个操作
create or replace trigger u_trigger
2 instead of
3 update on my_view
4 for each row
5    
6 begin
7   dbms_output.put_line('执行update替代触发器!');
8 end;

（四）设置触发器的开关（开启和禁用）

1.开启触发器

--开启所有的触发器(all triggers)
 SQL> alter table trg_table enable all triggers;
 
--开启某个触发器
 SQL> alter trigger trg_test enable;
 

2.禁用触发器

--禁用所有的触发器（all triggers）
 SQL> alter table trg_table disable all triggers;
 
Table altered
--禁用某个触发器
 SQL> alter trigger trg_test disable;
 

最后，怎么实现id字段自增呢？下面来演示

oracle实现自增id

1.创建一张table_test表

create table table_test

(
       id number(10) not null primary key,

       Name nvarchar2(120) not null,

       Age integer not null,

       Mobile varchar(12),

       Email varchar(50),

       Address nvarchar2(300)     

);

2.创建一个序列，序列名字叫seq_id_increment

–创建一个序列(序列名的规则一般建议是以SEQ开头，然后下划线，后面跟你的表名，表名前的T_可以去掉，然后以_Identity结尾，用来表示我这个序列是用在Id自增字段的序列)

  create sequence seq_id_increment 

   increment by 1 --每次增加几个，我这里是每次增加1

   start with 1   --从1开始计数

   nomaxvalue      --不设置最大值

   nocycle         --一直累加，不循环

   nocache;        --不建缓冲区

–你只有了表和序列还不够，还需要一个触发器来执行它

3.创建一个触发器 触发器的名字叫trg_id_increment

create trigger trg_id_increment before

insert on table_test for each row 

when(new.id is null)

begin

select seq_id_increment.nextval into:new.id from dual;

end;