[oracle]触发器第一篇：DML触发器

一.触发器概念

触发器是存储在服务器中的程序单元，当数据库中的某些事件发生时
，数据库自动启动触发器，执行触发器中的相应操作。

1.触发器是一种特殊的存储过程，具备事务的功能；
2.触发器不能被直接调用，而是由事件来触发；
3.触发器常用于加强数据的完整性约束和业务规则等。

简单点说：触发器就是个开关，灯就是开关触发后的操作，触动了开关灯就亮了。

二.触发器类型分类

1.DDL触发器（模式触发器）

在模式中执行DDL语句时执行。
DML触发器的限制：
1.不能使用控制语句：如commit、rollback、savepoint
2.由触发器所调用过程或函数也不能使用控制语句
3.不能使用long、long raw类型

2.数据库级触发器

在发生打开、关闭、登录和退出数据库等系统事件时执行。

3.DML触发器(本篇只讲这个触发器)

(1).DML触发器分类

行级触发器

  对DML语句修改的每一行都执行一次。

语句级触发器

  无论受影响行数是多少，都只执行一次。

instead of触发器

  用于用户不能直接使用DML语句修改的视图。

(2).DML触发器的限制：

  [1].不能使用控制语句：如commit、rollback、savepoint
  [2].由触发器所调用过程或函数也不能使用控制语句
  [3].不能使用long、long raw类型

三.DML触发器实例讲解

1.1.语句级触发器

注：目前演示的同步为了方便，是同步同一用户下的两张表，工作中实际操作往往是不同用户下的表。
需求：有一个学生表，还有一个结构完全一致的学生备份表。当我要往学生表里面增删改内容时，学生备份表要进行同步操作，使得两张表内容完全一致。

我们先只建一张学生表来看一下简单的触发器，这里的简单触发器指的是DML触发器的语句级触发器，语句级触发器是指每执行一次insert、update、delete语句后会触发一次。

--学生表
create table studentTest(
       id number(5) primary key,--学生id
       name varchar2(50) not null,--学生姓名
       classno number(5) not null,--班级号
       sex char(2) not null,--性别
       birthday date,--出生日期
       address varchar2(200)--住址
);

--一个基本的触发器(默认是语句级)
create or replace trigger stTriggerDemoInsert1
after insert--在新增后触发
on studentTest--针对studentTest这张表
declare
begin
  dbms_output.put_line('语句级触发器：学生表执行了一次insert语句。');
end;

--每个insert语句会触发一次语句级触发器
insert into studentTest values(1,'黄小花',101,'女',null,null);
insert into studentTest values(2,'李大傻',102,'男',to_date('2010-10-1','yyyy-MM-dd'),null);
insert into studentTest values(3,'花儿小华',103,'女',null,'桂林欧家村');
insert into studentTest values(4,'二狗子',101,'男',to_date('2009-01-25','yyyy-MM-dd'),'桂林秀峰区');
commit;

执行这4条insert语句后的output内容如下

1.2.行级触发器

那么现在我们新建学生备份表，再给学生备份表加两个触发器，分别是语句级触发器和行级触发器。行级触发器顾名思义，增删改一行就触发一次，n行就n次。

--学生备份表，结构和学生表完全一致
create table studentTestBackup as select * from studentTest where 1=2;--条件1=2表示只要结构不要数据

--语句级触发器
create or replace trigger stbTriggerDemoInsert1
after insert
on studentTestBackup
begin
  dbms_output.put_line('语句级触发器：学生备份表执行了一次insert语句。');
end;

--行级触发器
create or replace trigger stbTriggerDemo1insert2
after insert
on studentTestBackup
for each row--这一句指明了是行级触发器
begin
  dbms_output.put_line('行级触发器：学生备份表成功新增一行数据');
end;

--查询学生表内容并新增到学生备份表里
insert into studentTestBackup select * from studentTest;
commit;

很明显可以看到效果，这一条insert语句实际插入了4行数据，所以分别触发了4次行级触发器和1次语句级触发器。

现在学生表和学生备份表数据内容完全一致，如下

那么问题来了，上面是通过手动来同步学生备份表的，但这样显然很蠢很笨，那么我们如何使用触发器来自动进行同步呢？

1.3.触发器同步数据

我们需要的效果是，在增删改学生表后，自动的而非人工的去同步学生备份表，所以写如下的触发器。

1.3.1.新增的同步

:new表示的是新增后的这一行，行即对象，使用这个关键字来取得这一对象里的字段的值，从而新增给学生备份表。

--把之前的所有触发器删掉
drop trigger stTriggerDemoInsert1;
drop trigger stbTriggerDemoInsert1;
drop trigger stbTriggerDemoInsert2;

--使用行级触发器，每给学生表新增一行后，也给学生备份表新增一行。
create or replace trigger stTriggerSynchronizeInsert
after insert
on studentTest
for each row
begin
  insert into studentTestBackup values(:new.id,:new.name,:new.classno,:new.sex,:new.birthday,:new.address);
  dbms_output.put_line('行级触发器：学生表新增一行数据，且学生备份表同步了这一条数据');
end;

重新再新增一次数据

truncate table studentTest;
truncate table studentTestBackup;
commit;
insert into studentTest values(1,'黄小花',101,'女',null,null);
insert into studentTest values(2,'李大傻',102,'男',to_date('2010-10-1','yyyy-MM-dd'),null);
insert into studentTest values(3,'花儿小华',103,'女',null,'桂林欧家村');
insert into studentTest values(4,'二狗子',101,'男',to_date('2009-01-25','yyyy-MM-dd'),'桂林秀峰区');
commit;

查询学生备份表数据如下

select * from studentTestBackup;

1.3.2.修改的同步

--触发器同步数据：修改
create or replace trigger stTriggerSynchronizeUpdate
after update--修改后触发
on studentTest
for each row
begin
  update studentTestBackup set name=:new.name,classno=:new.classno,sex=:new.sex,birthday=:new.birthday,address=:new.address where id=:new.id;--一般id是不修改的，所以作为update的条件
  dbms_output.put_line('行级触发器：学生表修改了一行数据，且学生备份表同步了这一条数据');
end;

--修改数据
update studentTest set birthday=to_date('2008-08-08','yyyy-MM-dd'),address='中国香港九龙xx街xx号' where id=1;
commit;

select * from studentTestBackup;

1.3.3.删除的同步

注意用的是:old关键字而不是:new，如果是用:new的话，是指删除后，删除后都没有这一行数据了，就取不到这个对象了。

--触发器同步数据：删除
create or replace trigger stTriggerSynchronizeDelete
after delete
on studentTest
for each row
begin
  delete from studentTestBackup where id=:old.id;--注意:是old关键字，取得删除前的对象的字段
  dbms_output.put_line('行级触发器：学生表删除了一行数据，且学生备份表也同步删除了这一条数据');
end;

--删除3行数据
delete from studentTest where id between 1 and 3;
commit;

select * from studentTestBackup;

1.3.4.同一个触发器判断增删改

前面我们写了3个用于同步数据的触发器，这样显然有点麻烦且多余，能否合并成1个触发器呢？然后在同一个触发器里判断增删改操作，再执行不同的语句。

--删除之前的触发器
drop trigger stTriggerSynchronizeInsert;
drop trigger stTriggerSynchronizeUpdate;
drop trigger stTriggerSynchronizeDelete;
--把3个分别用于增删改的触发器合并成1个触发器
create or replace trigger stTriggerSynchronizeAll
after insert or update or delete
on studentTest
for each row
begin
  if inserting then--新增时
    insert into studentTestBackup values(:new.id,:new.name,:new.classno,:new.sex,:new.birthday,:new.address);
    dbms_output.put_line('判断：这是新增操作。学生表和学生备份表都新增了一行数据。');
  elsif updating then--修改时
    update studentTestBackup set name=:new.name,classno=:new.classno,sex=:new.sex,birthday=:new.birthday,address=:new.address where id=:new.id;
    dbms_output.put_line('判断：这是修改操作。学生表和学生备份表都修改了一行数据。');
  elsif deleting then--删除时
    delete from studentTestBackup where id=:old.id;
    dbms_output.put_line('判断：这是删除操作。学生表和学生备份表都删除了一行数据。');
  end if;
end;

1.4.带条件的触发器

即满足条件要求的数据时才触发

--带条件的触发器：满足条件要求的数据时才触发
create or replace trigger stbTriggerDemo
after insert--新增后触发
on studentTestBackup
for each row when (new.classno=103)--新增后，满足此条件满足则触发
begin
  dbms_output.put_line('行级触发器：学生备份表的该行学生是103班的');
end;

insert into studentTest values(5,'王五',103,'男',null,null);--会触发
insert into studentTest values(6,'赵六',104,'男',null,null);--不会触发
insert into studentTest values(7,'孙七',103,'男',null,null);--会触发
insert into studentTest values(8,'吉八',104,'男',null,null);--不会触发
commit;

select * from studentTestBackup;

插入了4行数据，其中两个是103班的，另外两个是104班的，可以看到触发了两次

学生备份表内数据

1.5.instead of触发器

instead of触发器主要是用于视图的，视图都是多个表的查询结果，一般来说是不可以对视图进行类似于增删改的操作的。而instead of触发器就是专门解决这个问题。
新建一个班级表

--班级表
create table classTest(
       id number(5) primary key,--班级号
       name varchar2(50) not null,--班级名称
       place varchar2(50)--所在位置
);

insert into classTest values(101,'普通1班','A栋教学楼403');
insert into classTest values(102,'普通2班','B栋教学楼112');
insert into classTest values(103,'尖子1班','学霸楼606');
commit;

select * from classTest;

当前学生表数据如下

两张表联表然后创建视图

--创建视图
create or replace view student_class_view
as
select s.id as studentNo,s.name as studentName,
s.classno,s.sex,s.birthday,
s.address as studentAddress,
c.name as className,c.place as classPlace
from studentTest s
inner join classTest c
on s.classno=c.id;

--查看视图
select * from student_class_view;

不能直接对多表联表的视图进行新增操作，如下语句执行后会报错

insert into student_class_view values(10,'张笨笨',104,'男',to_date('2007-08-20','yyyy-MM-dd'),'深圳','尖子2班','学霸楼707');--会报错

新建instead of触发器

create or replace trigger student_class_view_trigger
instead of insert
on student_class_view--针对的视图
for each row--行级
declare
  cursor stu_cur is select * from studentTest where id=:new.id;--游标：查询学生表是否存在该id的学生
  cursor class_cur is select * from classTest where id=:new.id;--游标：查询班级表是否存在该id的班级
  stu_row stu_cur%rowtype;
  class_row class_cur%rowtype;
begin
  open stu_cur;
  open class_cur;
  fetch stu_cur into stu_row;
  fetch class_cur into class_row;
  if stu_cur%notfound then--学生表查询不到该id的学生时，新增该学生
    insert into studentTest values(:new.studentNo,:new.studentName,:new.classNo,:new.sex,:new.birthday,:new.studentAddress);
    dbms_output.put_line('学生表无此学生，已新增。');
  else
    update studentTest set name=:new.studentName,classNo=:new.classNo,birthday=:new.birthday,address=:new.studentAddress where id=:new.studentNo;
    dbms_output.put_line('学生表有此学生，已修改。');
  end if;
  if class_cur%notfound then
    insert into classTest values(:new.classNo,:new.className,:new.classPlace);
    dbms_output.put_line('班级表无此班级，已新增。');
  else
    update classTest set name=:new.className,place=:new.classPlace where id=:new.classNo;
    dbms_output.put_line('班级表有此班级，已修改。');
  end if;
  close stu_cur;
  close class_cur;
end student_class_view_trigger;

我们再次执行之前的新增操作，发现可以成功新增了，效果如下

insert into student_class_view values(10,'张笨笨',104,'男',to_date('2007-08-20','yyyy-MM-dd'),'深圳','尖子2班','学霸楼707');--现在可以成功给视图添加数据了
commit;

再新增两条数据

insert into student_class_view values(1,'黄小花',105,'女',to_date('2011-01-01','yyyy-MM-dd'),'北京高新区','尖子3班','学霸楼808');--学生存在，班级不存在
insert into student_class_view values(11,'王蛋蛋',101,'男',null,'上海','普通1班','C栋教学楼305');--学生不存在，班级存在
commit;
select * from student_class_view;