Oracle 触发器

大学生创业支持：[url]http://sunzonebusiness.taobao.com[/url]
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;