触发器基础学习

1.首先说触发器的基本语法格式：

create [or replace] trigger 触发器名称  ---创建触发器 
{after|before} ---指定触发时机 
{insert or delete or update} ---指定触发事件 
of 字段名 ---所监控的表字段 
on 表名 ---所监控的表 
{referencing ---参照变量 
old as old_value new as new_value} 
{for each row|for each statement} ---指定触发的次数（行级触发和语句级触发） 
begin 
sql代码； ---触发器执行的相应操作 
end; 

--for each row 行级触发器，每操作一行就触发一次，例如：delete from table 删除100行，就触发100次(一行一次)。 --for each statement 语句级触发器，例如： delete from table 删除100行，则只触发一次(一条语句一次)。 --old_value 行变量，表示事件发生前旧数据所在的行，可通过:old_value.字段名 来调用相对应的字段值； --new_value 行变量，表示事件发生后新数据所在的行，可通过:new_value.字段名 来调用相对应的字段值；

2.利用例子实现触发器

（1）语句触发器。针对update，delete，insert等操作，执行一条语句触发器执行一次，与更新多少条数据无关。

首先创建一个表student：

 

create table student (
     stu_no number(6),
     stu_name varchar2(20),
     age number(3), 
     sex varchar2(10) 
);

 然后创建另一张日志表记录用户操作时间student_log：

create table student_log( 
     stu_user varchar(20), 
     modfiy_time date
 ); 

 

在student表上创建语句触发器stu_trig1，通过触发器记录操作的用户以及时间：

create or replace trigger stu_trig1 
before insert or update or delete --插入或更新或删除就触发下面时间 
on student 
begin 
  insert into student_log values(user,sysdate); --向student_log中插入当前用户和时间 
end; 

    向student表中插入一条数据：

         insert into student values('1','dwj','23','male');

    日志表student_log中多出一条记录：当前用户名，当前时间 。

    自己多加入几条记录，利用一条语句更新多条记录，发现日志student_log中只增加一条记录，这就是语句级触发器

    这里没有for each row和for each statement，默认的是for each statement(语句级触发器)。  

（2）行级触发器。语句中包含for each row子句；在before...for each row中，用户可以引用受到影响的行值。

    定义一个触发器stu_trig2，当插入或更新一个学生的学号信息时，如果学号不等于8，就将其年龄设置为20。

create trigger stu_trig2 
before insert or update 
on student 
referencing 
old as old_value 
new as new_value 
for each row                              --注意for each row的位置，在when前面
when (new_value.stu_no<>8) 
begin
    :new_value.age:=20; 
end; 

    这里执行插入语句：

1. insert into student values('8','xxx','23','female');
2. insert into student values('9','zzz','23','male');
3. update student set  ...  where stu_no=?       

    执行上面第1条语句时，由于stu_no=8,没有触动触发器stu_trig2，这条数据正常插入；执行第二条语句时，stu_no<>8,触动触发器stu_trig2,则这条数据对应的age字段变为20，这时数据库中插入的是'20'而不是'23'。

    注意：

    referencing子句：执行DML语句之前的值的默认名称是:old,之后的值是:new

    insert操作只有:new

    delete操作只有:old

    update操作两者都有

    referencing子句只是将new和old重命名为new_value和old_value，目的是避免混淆。

（3）instead of 触发器

    主要被用于更新那些没有办法通过正常方法更新的视图。例如，通常不能再一个基于连接的视图上进行delete操作。基于多个表的视图必须使用instead of触发器，因为不用触发器不能正常删除、更新、插入。不能在带有with check option定义的视图中创建instead of 触发器。

    简单例子：

    通过对学生表（stu）和成绩表（cour）视图sc_view的更新，实现对数据表的插入操作。

  

--创建学生表
create table stu(
     stu_no number(6),
     stu_name varchar2(20)
);

 

--创建成绩表
create table cour(
     s_no number(6),
     grade number(4,1)
);

 

--视图，显示学号，姓名和成绩
create view sc_view 
as 
select stu.stu_no,stu_name,grade from stu,cour;

 

--向视图中插入一组数据
 insert into sc_view values(5,'lili',85);
--执行这条语句时出错，因为这个视图是基于2个表创建的。

 

--使用instead of触发器实现数据表的更新
create trigger sc_view_trig
 instead of insert                    --注意instead of 的位置取代before|after
 on sc_view                            --针对的视图
 begin
 insert into stu values(:new.stu_no,:new.stu_name);
 insert into cour values(:new.stu_no,:new.grade);
 end;
---当对视图sc_view进行insert操作时，触动这个触发器，这个触发器的作用是把新插入的stu_no,stu_name插入stu表中，把stu_no,grade插入cour表中。

    再执行上面的insert语句，视图和表中都能正常插入数据了。

 

（4）用户事件触发器

    用户事件触发器（DDL触发器），当发出create、drop、alter、truncate命令时会触发已定义好的DDL触发器，这种触发器可以用来监控某个用户或整个数据库的所有对象的结构变化。

 create or replace trigger trigger_name
 before|after 
 create|alter|drop|truncate
 on schema|database

 
（5）系统事件触发器

      当startup、shutdown、logon、logoff数据库时就会触发系统事件触发器，这种触发器可以用来监控数据库是什么时候关闭/打开，或用户logon/logoff情况。

 create or replace trigger trigger_name
 before|after  startup|shutdown|logon|logoff
 on database
 ---注：on作用的一定是database，而不是其他用户
系统触发器示例：

记录数据库的关闭时间例子：
(shutdown类型要用关键字before, startup 用after)
--首先创建一个表来存放关闭时间
 create table time_record(tt_time varchar2(50));
--触发器的创建
 create or replace trigger db_shutdown
 before shutdown
 on database
 begin
 insert into time_record values('db shutdown'||tochar(sysdate,'yyyy-MM-dd hh:mm:ss'));
 end;

--禁用和启用触发器
  alter trigger <trigger_name>disable;
   alter trigger<trigger_name>enable;

--事务处理
  在触发器中，不能使用commit/rollback 因为DDL语句具有隐式的commit，所以也不允许使用。