oracle的学习历程 2017/9/2

触发器

用来监视数据库的操作。

基本原理

触发器类似于函数以及过程等，但与他们不同的是触发器是oracle自动执行的。对用户来说是不可见的。

触发器包括如下三种：

DML触发器：对表或视图执行DML操作的时候触发。（insert,update,delete）

instead of 触发器：制定一在视图上，用来替换实际的操作语句。

系统触发器：

对数据库进行操作时触发。

相关概念

触发事件：引起触发器被触发的事件。如dml（insert，update，delete）,ddl（create，alter，drop）,数据系统事件（启动或退出，异常），用户事件(登陆或者退出数据库)

触发条件：是由when子句指定的逻辑表达式，当该值为true的时候，触发器才会被执行。否则不会执行。

触发对象：表，视图，模式，数据库。

触发操作：执行部分

触发时机：是指触发器的触发时间，如果指定为BEFORE，则表示在执行DML操作之前触发，这种是为了防止某些错误操作的发生。

                                                          如果指定为after，表示在dml操作之后触发，以便记录该操作或某些事后处理。

触发子类型：分为行触发和语句触发，行触发即对每一行操作时都要出发，而语句出发只对操作触发一次，一般进行sql语句操作时都是行触发。只有对整张表做安全检查的时候，才出发语句出发，若忽略此项，则默认为语句触发。除此之位，触发器还有两个相关值，分别对应触发行中的旧表值(old)和新表值(new)。

触发器谓词

    ORACLE 提供三个参数INSERTING, UPDATING,DELETING 用于判断触发了哪些操作。

谓词                                  行为

INSERTING              如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE

UPDATING              如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE

DELETING               如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

创建触发器

触发器的语句是create trigger

create or replace trigger 触发器名

触发条件

触发体 （就是pl/sql语句块）

举例：

CREATE OR REPLACE TRIGGER tr_del_emp                        定义一个触发器tr_del_emp

  BEFORE DELETE --指定触发时机为删除操作前触发

   ON   scott.emp

  FOR EACH ROW   --说明创建的是行级触发器

when(new.Tname='David')  --这一部分是触发条件

declare                             --触发体

begin

exception

end；         

执行触发器

对一张表上的触发器最好加以限制，否则会因为触发器过多而加重负载，影响性能。另外，最好将一张表的触发事件编写在一个触发体重，也可以大大改善性能。

比如：

CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE  --这一步体现了将所有的触发器都放在tr_dept_time中

ON departments

BEGIN

 IF(TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00')THEN

    RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');

 ENDIF;

END;

删除触发器

drop trigger 触发器名

或者 create or replace trigger 触发器名（这种方法适用于触发器相同，但不想删除重建，可以用这种方式直接对已有的触发器进行修改）

编写触发器时，需要注意以下几点：

1、触发器不接受参数。

2、一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

这12种类型分别是：

BEFORE INSERT

BEFORE INSERT FOR EACH ROW

AFTER INSERT

AFTER INSERT FOR EACH ROW

 

BEFORE UPDATE

BEFORE UPDATE FOR EACH ROW

AFTER UPDATE

AFTER UPDATE FOR EACH ROW

 

BEFORE DELETE

BEFORE DELETE FOR EACH ROW

AFTER DELETE

AFTER DELETE FOR EACH ROW

3、在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

4、触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

5、在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

6、触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

7、在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

8、在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能是表中的任何long和blob列。

9、不同类型的触发器(如DML触发器、INSTEADOF触发器、系统触发器)的语法格式和作用有较大区别。

创建替代(INSTEAD OF)触发器

基本语法

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name --只能定义在视图上
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;

INSTEAD OF 选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器，而不能对表、模式和数据库建立INSTEAD OF 触发器。

    REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

创建INSTEAD OF触发器需要注意以下几点：

只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

不能指定BEFORE 或 AFTER选项。

FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。

没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创建DML触发器就可以了。

举个例子：

使用情景：

CREATE   OR   REPLACE   VIEW  emp_view  AS  
SELECT  deptno,  count ( * ) total_employeer,  sum (sal) total_salary 
FROM  emp  GROUP   BY  deptno;

在此视图中直接删除是非法操作：

SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10

错误提示

ERROR 位于第 1 行:

ORA-01732: 此视图的数据操纵操作非法

但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：

CREATE OR REPLACE TRIGGER emp_view_delete
   INSTEAD OF DELETE ON emp_view

 FOR EACH ROW
BEGIN
   DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete; 

DELETE FROM emp_view WHERE deptno=10; 

DROP TRIGGER emp_view_delete;

DROP VIEW emp_view;