Oracle 触发器

SQL> set serveroutput on;
SQL> remark 触发器的特点：触发器存储在数据库中，并与所相关的表是分别存储的，触发器只能在表上进行定义。
SQL> remark 触发器的作用：自动的生成导出的列值；防止无效的事务；实施更为复杂的安全性检查；提供高级的审计；维护同步表的复制；
SQL> remark 一个触发器有如下的几个基本要素：触发源、触发时机、触发事件、触发器的限制、触发器的动作；
SQL> remark 触发器的分类：数据库触发器和DML触发器
SQL> remark 如果是每条修改的语句都触发的触发器成为行级触发器，如果一条DML无论修改多少条记录，都只会触发一次的触发器称为语句级触发器。如果对于一个DML 语句无法法直接修改的触发器会启用替代触发器(instead of)
SQL> remark 行级触发器中有两个内置的对象 new old
SQL> remark 触发器案例：
SQL> remark 首先创建一张表
SQL> create table tritest
  2  (
  3  stuid int,
  4  stuname char(5),
  5  stuindex int
  6  );

Table created.

SQL> remark 创建触发器
SQL> create or replace trigger over_tri
  2  before insert on tritest
  3  for each row
  4  begin
  5  if:new.stuindex>=30 then
  6  raise_application_error(-20001,'超出一个班的范围');
  7  end if;
  8  end;
  9  /

Trigger created.

SQL> remark 注：在条件表达式中new old 对象不需要添加 ：
SQL> create or replace trigger over_tri1
  2  before insert on tritest
  3  for each row
  4  when(new.stuid<2000)
  5  begin
  6  if:new.stuindex>=30 then
  7  raise_application_error(-20001,'超出一个班的范围');
  8  end if;
  9  end;
10  /

Trigger created.

SQL> insert into tritest values(1000,'Mike',40);
insert into tritest values(1000,'Mike',40)
          *
ERROR at line 1:
ORA-20001: 超出一个班的范围
ORA-06512: at "SYSTEM.OVER_TRI1", line 3
ORA-04088: error during execution of trigger 'SYSTEM.OVER_TRI1'


SQL> remark 当插入一条值的时候触动了触发器，并抛出对应的错误；
SQL> remark 语句级的触发器是在触发的时候无论DML语句修改多少条记录，触发器的主体只会被执行一次；
SQL> create or replace trigger updateemptri
  2  before insert or update or delete
  3  on emp
  4  begin
  5  if user not in('scott') then
  6  raise_application_error(-20001,'该用户不能操作此表');
  7  end if;
  8  end;
  9  /
on emp
   *
ERROR at line 3:
ORA-00942: table or view does not exist


SQL> edit;
Wrote file afiedt.buf

  1  create or replace trigger updateemptri
  2  before insert or update or delete
  3  on scott.emp
  4  begin
  5  if user not in('scott') then
  6  raise_application_error(-20001,'该用户不能操作此表');
  7  end if;
  8* end;
SQL> /

Trigger created.

SQL> delete from scott.emp where empno=7369;
delete from scott.emp where empno=7369
                  *
ERROR at line 1:
ORA-20001: 该用户不能操作此表
ORA-06512: at "SYSTEM.UPDATEEMPTRI", line 3
ORA-04088: error during execution of trigger 'SYSTEM.UPDATEEMPTRI'


SQL> remark 替代触发器的使用：替代触发器被激发来替代执行修改视图的DML 语句；
SQL> remark 不可变更的视图如下几种情况：
SQL> remark 集合操作(union , union all , minus)
SQL> remark 分组计算函数(sum,avg...)
SQL> remark connect by 或 start with
SQL> remark 去除多行：distinct
SQL> remark 对于这些视图的DML 操作不能直接操作，而使用触发器进行转换；
SQL> remark 语法：
SQL> remark create or replace trigger 触发器名称 {before|after|instead of} 触发事件 referencing_clause [whe [when 触发条件] [for each row] begin ......end;
SQL> remark referencing_clause  用来引用正在处于修改状态下的行中的数据；
SQL> remark for each row  指定触发器是行级触发器，如果不指定那么是语句级触发器；
SQL> remark 对替代触发器进行测试：
SQL> remark 首先创建三个表进行数据测试
SQL> create table student
  2  (
  3  stuid int primary key,
  4  stuname varchar(10)
  5  )
  6  ;

Table created.

SQL> remark 创建课程表
SQL> create table subject
  2  (
  3  pid int primary key,
  4  pname varchar(10)
  5  );

Table created.

SQL> remark 创建成绩表
SQL> create table score
  2  (
  3  cid int primary key,
  4  stuid int references student,
  5  pid int references subject,
  6  computer number
  7  );

Table created.

SQL> remark 创建视图
SQL> create view stu_score
  2  as
  3  select cid,student.stuid,stuname,subject.pid,pname,computer
  4  from score , student,subject
  5  where score.stuid = student.stuid and score.pid = subject.pid;

View created.

SQL> remark 插入测试数据
SQL> insert into student values(1,'Tom');

1 row created.

SQL> insert into student values(2,'Tom2');

1 row created.

SQL> insert into student values(3,'Tom3');

1 row created.

SQL> insert into subject values(10,'math');

1 row created.

SQL> insert into subject values(20,'chinese');

1 row created.

SQL> insert into score values(1,1,10,80);

1 row created.

SQL> insert into score values(1,2,10,90);
insert into score values(1,2,10,90)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C005257) violated


SQL> insert into score values(2,2,10,90);

1 row created.

SQL> insert into score values(3,2,20,70);

1 row created.

SQL> remark 创建一个触发器
SQL> create or replace trigger stu_score
  2  instead of insert or update or delete
  3  on stu_score
  4  for each row
  5  begin
  6  if inserting then
  7  insert into student values(:new.stuid,:new.stuname);
  8  insert into subject values(:new.pid,:new.pname);
  9  insert into score values(:new.cid,:new.stuid,:new.pid,:new.computer);
10  dbms_output.put_line('已经成功的想三张表中插入了数据！');
11  elsif updating then
12  null;
13  elsif deleting then
14  null;
15  end if;
16  end;
17  /

Trigger created.

SQL> remark 动作指令：inserting updating deleting 可以作为条件进行判断
SQL> remark instead of  替代触发器
SQL> remark 变异表
SQL> remark 变异表：是触发器触发源语句正在修改的表或者被修改表的级联表；
SQL> remark .........................................................
SQL> remark 触发器的管理：
SQL> remark 对于已经创建的触发器可以通过启用和禁用触发器进行管理。
SQL> remark 对单一的触发器进行管理的语法:alter trigger 触发器名称 disable|enable
SQL> remark　如果一个表上的触发器很多，可以对一个表上的所有触发器进行禁用；
SQL> remark alter table 表名 disable |enable all triggers;
SQL> spool off;