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 --创建表
       ( 
        stuid int, 
        stuname char(5), 
        stuindex int 
      );

      Table created.

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

         Trigger created.

SQL> remark 注：在条件表达式中new old 对象不需要添加 ：
SQL> create or replace trigger over_tri1 
            before insert on tritest 
            for each row 
            when(new.stuid<2000) 
            begin 
           if:new.stuindex>=30 then 
           raise_application_error(-20001,'超出一个班的范围'); 
           end if; 
           end; 
         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 
            before insert or update or delete 
             on emp 
            begin 
             if user not in('scott') then 
             raise_application_error(-20001,'该用户不能操作此表'); 
             end if; 
            end;  
          on emp 
         on emp 
ERROR at line 3:
ORA-00942: table or view does not exist

SQL> edit;
Wrote file afiedt.buf 
  create or replace trigger updateemptri 
     before insert or update or delete 
    on scott.emp 
    begin 
    if user not in('scott') then 
    raise_application_error(-20001,'该用户不能操作此表'); 
    end if; 
   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 
  ( 
  stuid int primary key, 
  stuname varchar(10) 
  ) ;

Table created.

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

Table created.

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

Table created.

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

View created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

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);

row created.

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

row created.

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

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;