【Oracle】常用对象之触发器

一、什么是触发器？

触发器trigger，是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用，有行级触发器和语句级触发器之分。

• 行级触发器：触发的表中的每一条数据改变时就会被触发一次。
• 语句级触发器：在某些语句执行前或执行后被触发。

接着，对触发器的语法和基本操作认识一下：

1、创建触发器（定义）

-- 触发时间参数：before表示在数据库动作之前执行触发器，after则是之后；
-- 触发事件：如insert/delete/update；
-- 表名：触发器触发动作指定的表；
-- for each row：表每一行触发一次，没有这个选项，对整个表触发执行一次；
create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
  pl/sql语句
end

2、触发器其他基本操作（删除，禁用/启用）

--删除触发器
drop trigger  触发器名;
--禁用指定的触发器
alter trigger 触发器名 disable;
--启用指定的触发器
alter trigger 触发器名 enable;
--禁用指定表中的所有触发器
alter table 表名 disable all triggers;
--启用指定表中的所有触发器
alter table 表名 enable all triggers;

举个例子，实际感受下：

-- 在表 ai_carsharing_record 插入数据后，打印"数据插入成功啦！"。
create or replace trigger tcar
after insert
on  ai_carsharing_record
declare
begin
dbms_output.put_line('数据插入成功啦！');
end;

结果：

二、为什么要用触发器？

实际工作中，可能会遇到如下场景，则需要考虑使用触发器：

• 允许/限制对表的修改；
• 自动生成派生列，比如实现字段自增；
• 强制数据一致性，比如数据的备份和同步；
• 启用复杂的业务逻辑；
• 提供审计和日志记录等等。

因此，触发器的使用还是挺广泛的，根据实际情况选择使用。当然，实际开发基本很少使用触发器，因为触发器的效率不高，而且容易导致多个表发生错误，寻找错误相当麻烦，通常使用存储过程。

三、Oracle中怎么使用触发器？

通过自己以前写过的两个例子演示下：

1. 通过 触发器(trigger) 和 序列(sequence) 的组合实现指定字段自增

SQL> set serverutput on 

-- 创建基础表
SQL> create table ai_site(
    site_id number(10))  not null primary key,
    site_name varchar2(50),
    lat number(15,10),
    lng number(15,10),
    description varchar2(100)
    );
表已创建。

-- 创建序列
SQL> create sequence seq_site  increment by 1 start 
    with 1 minvalue 1 maxvalue 9999999999999 nocache order;
序列已创建。

-- 创建触发器（注意begin-end块里的语法，避免写错报：警告: 创建的触发器带有编译错误。）
SQL> create or replace trigger tsite
before insert  on ai_site
for each row
declare
begin
 select seq_site.nextval into :new.site_id from dual;
end;
/
触发器已创建

-- 测试触发器是否生效（site_id被设置为了主键不为空，这里利用触发器执行序列自增）
SQL> insert into ai_site(site_name,lat,lng,description) values('科大讯飞北门',null,null,'云飞路与永和路交口附近');
已创建 1 行。

SQL> commit;
提交完成。

-- 禁用基础表的所有触发器
SQL> alter table ai_site disable all triggers;
表已更改。

-- 测试效果
SQL> insert into ai_site(site_name,lat,lng,description) values('科大讯飞北门',null,null,'云飞路与永和路交口附近');
insert into ai_site(site_name,lat,lng,description) values('科大讯飞北门',null,null,'云飞路与永和路交口附近')
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("XIAOPINGZI"."AI_SITE"."SITE_ID")

2. 通过 触发器(trigger) 实现数据的备份和同步

SQL> set serverutput on 
SQL>set linesize 200;

-- 备份主表结构和数据
SQL> create table ai_carsharing_record_bak 
    as select * from ai_carsharing_record where 1=1;

-- 先查询一下主表和备份表的数据  
SQL> select "car_way" from ai_carsharing_record where "record_id"=1;
car_way
--------------------------------------------------
皖水公寓

SQL> select "car_way" from ai_carsharing_record_bak where "record_id"=1;
car_way
--------------------------------------------------
皖水公寓

SQL> create or replace trigger trecord
after update on ai_carsharing_record
for each row
begin
    update ai_carsharing_record_bak set "car_way"=:new."car_way" ,  "car_from"=:new."car_from" where  "record_id"=:new."record_id";
end;
/
触发器已创建

--  测试
SQL>   update ai_carsharing_record set "car_way"='华纺新华成' where  "record_id"=1;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select "car_way" from ai_carsharing_record_bak where "record_id"=1;
car_way
--------------------------------------------------
华纺新华成