Oracle触发器

一、 DML触发器

数据操纵语言触发器，创建在表上，由DML事件引发的触发器。

1、DML事件：
INSERT：在表或视图中插入数据时触发
UPDATE：修改表或视图中的数据时触发
DELETE  在删除表或视图中的数据时触发

 

2、DML触发器的要点：
（1）确定触发的表，即在其上定义触发器的表。
（2）确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种
（3）确定触发时间。触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。 （4）确定触发级别，有语句级触发器和行级触发器两种。
 语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。

3、DML触发器先后顺序：
由于在同一个表上可以定义多个DML触发器，它们执行的先后顺序是：
（1）如果存在语句级BEFORE触发器，则先执行一次语句级BEFORE触发器。
（2）在SQL语句的执行过程中，如果存在行级BEFORE触发器，
        则SQL语句在对每一行操作之前，都要先执行一次行级BEFORE触发器，然后才对行进行操作。
       如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。
（3）如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。

4、其它要点：
（1）如果有多个触发器被定义成为相同时间、相同事件触发，且最后定义的触发器是有效的，
        则最后定义的触发器被触发，其他触发器不执行。
（2）一个触发器可由多个不同的DML操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来
        区别不同的DML操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。
（3）在行级触发器中，用:new 和:old(称为伪记录)来访问数据变更前后的值。
       但要注意，INSERT语句插入一条新记录，所以没有:old记录，而DELETE语句删除掉一条已经存在的记录，
       所以没有:new记录。UPDATE语句既有:old记录，也有:new记录，分别代表修改前后的记录。
       引用具体的某一列的值的方法是：:old.字段名或:new.字段名
（4）触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的

        存储过 程。

 

5、DML触发器的创建，创建DML触发器需要CREATE TRIGGER系统权限。
   创建DML触发器的语法如下：

CREATE [OR REPLACE] TRIGGER 触发器名 
{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...] 
ON 表名 
WHEN 触发条件 
[FOR EACH ROW] 
DECLARE 
     声明部分 
BEGIN 
    主体部分 
END; 

 

 语法说明：

OR REPLACE：表示如果存在同名触发器，则覆盖原有同名触发器。
BEFORE、AFTER和INSTEAD OF：说明触发器的类型。
WHEN 触发条件：表示当该条件满足时，触发器才能执行。
触发事件：指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接。
对于UPDATE事件，还可以用以下形式表示对某些列的修改会引起触发器的动作：
UPDATE OF 列名1，列名2...
ON 表名：表示为哪一个表创建触发器。
FOR EACH ROW：表示触发器为行级触发器，省略则为语句级触发器。

6、触发器的删除
删除触发器的语法如下：DROP TIRGGER 触发器名

7、触发器状态的设置
命令语法如下：ALTER TRIGGER 触发器名 {DISABLE|ENABLE}
其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。

8、触发器具体事例
（1）创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对EMP表的操作进行记录，存储到日志记录表
LOGS（LOG_ID，LOG_TABLE，LOG_DML，LOG_KEY_ID，LOG_DATE，LOG_USER）中，
用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。
SQL语句如下:

CREATE OR REPLACE TRIGGER DML_LOG  
BEFORE --触发时间为操作前  
DELETE OR INSERT OR UPDATE -- 由三种事件触发  
ON emp  
FOR EACH ROW -- 行级触发器  
BEGIN  
	IF INSERTING THEN   
	 	INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,
			 'EMP','INSERT',:new.empno,SYSDATE,USER);  
	ELSIF DELETING THEN  
		INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,
			 'EMP','DELETE',:old.empno,SYSDATE,USER);  
	ELSE  
	 	INSERT INTO logs VALUES(logs_id_squ.NEXTVAL,
	 		'EMP','UPDATE',:new.empno,SYSDATE,USER);  
	END IF;  
END;  

 

(2) 创建一个行级触发器LOG_SAL，记录对职务为CLERK的雇员工资的修改，且当修改幅度超过200时才进行记录。
用WHEN条件限定触发器。触发器语句如下：

CREATE OR REPLACE TRIGGER log_sal  
BEFORE  
UPDATE OF sal  
ON emp  
FOR EACH ROW  
WHEN (new.job='CLERK' AND (ABS(new.sal-old.sal)>200))   
DECLARE   
	v_no NUMBER;  
BEGIN  
	SELECT COUNT(*) INTO v_no FROM logerr;   
	INSERT INTO logerr VALUES(v_no+1,'雇员'||:new.ename||'的原工资：'
		||:old.sal||'新工资：'||:new.sal);  
END;

 

 （3）创建触发器CHECK_SAL，当对职务为CLERK的雇员的工资修改超出500至2000的范围时，进行限制。
    触发器语句如下：

CREATE OR REPLACE TRIGGER CHECK_SAL  
BEFORE  
UPDATE  
ON emp  
FOR EACH ROW  
BEGIN  
	IF :new.job='CLERK' AND (:new.sal<500 OR :new.sal>2000) THEN  
	RAISE_APPLICATION_ERROR(-20001, '工资修改超出范围,操作取消！');  
	END IF;  
END;

 注：由于触发器是BEFORE类型，因此触发器先执行，触发器因异常而终止，SQL语句的执行就会取消。

（4）创建一个行级触发器CASCADE_UPDATE，当修改部门编号时，EMP表的相关行的部门编号也自动修改。
 该触发器称为级联修改触发器。触发器语句如下：

CREATE TRIGGER CASCADE_UPDATE  
AFTER   
UPDATE OF deptno  
ON DEPT  
FOR EACH ROW  
BEGIN  
 	UPDATEEMP SET EMP.DEPTNO=:NEW.DEPTNO  
	WHERE EMP.DEPTNO=:OLD.DEPTNO;  
END;

 注：UPDATE OF deptno表示只有在修改表的DEPTNO列时才引发触发器，对其他列的修改不会引起触发器的动作。

（5）将插入的雇员的名字变成以大写字母开头。
  触发器语句如下：

CREATE OR REPLACE TRIGGER INITCAP  
BEFORE INSERT   
ON EMP  
FOR EACH ROW  
BEGIN  
	:new.ename:=INITCAP(:new.ename);  
END; 

 

（6）创建一个语句级触发器CHECK_TIME，限定对表EMP的修改时间为周一至周五的早8点至晚5点。
   触发器语句如下：

CREATE OR REPLACE TRIGGER CHECK_TIME  
BEFORE  
UPDATE OR INSERT OR DELETE   
ON EMP  
BEGIN  
	IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))   
	OR TO_CHAR(SYSDATE,'HH24')< '08'   
	OR TO_CHAR(SYSDATE,'HH24')>='17' THEN  
		RAISE_APPLICATION_ERROR(-20500,'非法时间修改表错误！');  
	END IF;   
END; 

 

 二、DDL事件触发器和数据库事件触发器

DDL事件触发器:数据定义语言触发器，定义在模式上，触发事件是数据库对象的创建和修改。
数据库事件触发器:定义在整个数据库或模式上，触发事件是数据库事件。

1、 触发器事件：
（1）DDL事件
   CREATE  在创建新对象时触发
   ALTER   修改数据库或数据库对象时触发
   DROP    删除对象时触发
（2）数据库事件(5种)  
   STARTUP  数据打开时触发 
   SHUTDOWN    在使用NORMAL或IMMEDIATE选项关闭数据库时触发 
   LOGON    当用户连接到数据库并建立会话时触发 
   LOGOFF   当一个会话从数据库中断开时触发  
   SERVERERROR 发生服务器错误时触发

2、触发器要点
对于模式级触发器，为自己的模式创建触发器需要CREATE TRIGGER权限，如果是为其他模式创建触发器，
需要CREATE ANY TRIGGER权限。
创建数据库级触发器需要ADMINISTER DATABASE TRIGGER系统权限，一般只有系统管理员拥有该权限。

3、触发器创建语法：
数据库事件或模式事件触发器的创建语法如下：

 

CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER }
{DDL事件1 [DDL事件2...]| 数据库事件1 [数据库事件2...]}
ON {DATABASE| [模式名]SCHEMA }
[WHEN (条件)]
DECLARE
	声明部分
BEGIN
	主体部分
END;

 

语法说明：
其中：DATABASE表示创建数据库级触发器，数据库级要给出数据库事件；
SCHEMA表示创建模式级触发器，模式级要给出模式事件或DDL事件。

4、触发器事件属性
（1）Sys.sysevent，   适用于所有类型，返回触发器触发事件字符串 
（2）Sys.instance_num ，  适用于所有类型， 返回Oracle实例号 
（3）Sys.database_name，  适用于所有类型，返回数据库名字 
（4）Sys.server_error(stack_position)，  适用于SERVERERROR类型，
                                                              从错误堆栈指定位置返回错误号，参数为1表示最近的错误。 
（5）Is_servererror(error_number)， 适用于SERVERERROR类型，
                                                         判断堆栈中是否有参数指定的错误号 
（6）Sys.login_user，适用于所有类型，返回导致触发器触发的用户名 
（7）Sys.dictionary_obj_type， 适用于CREATE、ALTER、DROP类型，
                                                  返回DDL触发器触发时涉及的对象类型 
（8）Sys. dictionary_obj_name， 适用于CREATE、ALTER、DROP类型，
                                                     返回DDL触发器触发时涉及的对象名称 
（9）Sys.des_encrypted_password，适用于CREATE、ALTER、DROP类型，
                                                        创建或修改用户时，返回加密后的用户密码

 

5、触发器具体事例
（1）创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表。
    步骤1：创建数据库STARTUP事件触发器

CREATE OR REPLACE TRIGGER INIT_LOGON  
AFTER  
STARTUP   
ON DATABASE  
BEGIN  
	DELETE FROM userlog;  
END;

 步骤2：创建数据库LOGON事件触发器

CREATE OR REPLACE TRIGGER DATABASE_LOGON  
AFTER  
LOGON   
ON DATABASE  
BEGIN  
	INSERT INTO userlog   
	VALUES(sys.login_user,sysdate);  
END;

 

（2）创建STUDENT_LOGON模式级触发器，专门记录STUDENT账户的登录时间。

CREATE OR REPLACE TRIGGER STUDENT_LOGON   
AFTER  
LOGON ON STUDENT.SCHEMA   
BEGIN    
	INSERT INTO userlog  
	VALUES(sys.login_user,sysdate);  
END;

  说明：为当前模式创建触发器，可以省略SCHEMA前面的模式名。

（3）通过触发器阻止对emp表的删除

CREATE OR REPLACE TRIGGER NODROP_EMP  
BEFORE  
DROP ON SCHEMA   
BEGIN  
	IF Sys.Dictionary_obj_name='EMP' THEN  
		RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');  
	END IF;   
END;

 说明：该触发器阻止在当前模式下对emp表的删除，但不阻止删除其他对象。
Sys.Dictionary_obj_name属性返回要删除的对象名称。

三、替代触发器

替代触发器，创建在视图上，用来替换对视图进行的插入、删除和修改操作。

1、触发器具体事例
（1）在emp表的视图上，通过触发器修改emp表。

  CREATE OR REPLACE TRIGGER change_name  
    INSTEAD OF INSERT ON emp_name  
    DECLARE  
    	V_EMPNO NUMBER(4);  
    BEGIN  
        SELECT MAX(EMPNO)+1 INTO V_EMPNO FROM EMP;  
        INSERT INTO emp(empno,ename)   
        VALUES(V_EMPNO,:new.ename);  
    END;

  说明：向视图直接插入雇员名将会发生错误，因为emp表的雇员编号列不允许为空。通过创建替代触发器，
     将向视图插入雇员名称转换为向emp表插入雇员编号和雇员名称，雇员编号取当前的最大雇员编号加1。

（2）在emp表的视图emp_name上，通过触发器阻止对emp表的删除。阻止通过视图删除雇员，
       并显示用户自定义错误信息。

  CREATE OR REPLACE TRIGGER delete_from_ename  
    INSTEAD OF DELETE ON emp_name  
  BEGIN  
     RAISE_APPLICATION_ERROR(-20006,'错误信息：不能在视图中删除emp表的雇员！');  
  END;

 说明：可以通过视图emp_name对雇员进行删除，比如执行DELETE FROM emp_name语句将删除雇员表的
全部雇员。但是由于在emp_name视图中只能看到一部分雇员信息，所以删除可能会产生误操作。
通过定义一个替代触发器，可阻止通过emp_name视图对emp表雇员进行删除，但不阻止直接对emp表进行删除。

四、查看触发器

1、查看触发器的定义内容：

SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME='CHECK_TIME'; 

 

评论

2 楼 sunzeping 2013-11-07  

写的不错，内容介绍较全面、很细；格式整齐，看起来舒服；

1 楼 火云剑Shanke 2013-01-01  

Hibenate的Many-to-one更新触发器执行两次,知道这是为什么吗？
http://www.iteye.com/problems/91391