ORACLE TRIGGER

今天在做项目维护中，发现项目中有很多的触发器，以前也没有对trigger有过了解，今天花了点时间学习了一下TRIGGER：

触发器有三种类型:

1、DML触发器

2、INSTEAD OF触发器

3、系统触发器

A、触发器类似存储过程和函数,都有声明,执行和异常处理过程的PL/SQL块.简单的来说

B、触发器就是在对数据库执行某些操作时(如:INSERT,UPDATE,DELETE),而被动发生的一些事件.

C、触发器在数据库里以独立的对象存储,所以可以重名,它与存储过程和函数不同的是,触发器是由一个事件来启动的,不需要显示调用才执行.

( 注意:触发器不能接收参数,一个表上最多12个触发器,

同一时间,同一事件,同一类型的触发器只能有一个,触发器越多,对表的DML操作的性能影响就越大.

触发器最大为32KB,可以通过触发器中调用过程解决.

触发器的执行部分只能用DML语句(select,insert,update,delete),不能使用DDL语句(create,alter,drop).

触发器中不能包含事务控制语句(commit,rollback,savepoint),因为触发器是触发语句的一部分,和触发语句一起提交回退.

触发器主体中调用的任何过程,函数都不能使用事务控制语句.

触发器主体中不能申明任何long和blob变量,新值和旧值也不能是表中的任何long和blob列.

)

触发器语法:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name 

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

INSERTING:当触发事件是INSERT时,取值为TRUE,否则为FALSE

UPDATING:当触发事件是UPDATE时,取值为TRUE,否则为FALSE

DELETING:当触发事件是DELETE时,取值为TRUE,否则为FALSE

FOR EACH ROW:表示每一条记录都会产生触发,而默认的语句及只会产生一次触发.

下面是我自己做的几个关于触发器的例子：

CREATE TABLE T_TRIGGER AS SELECT OBJECT_ID,OBJECT_TYPE,STATUS FROM ALL_OBJECTS;

CREATE TABLE T_TRIGGER_LOG AS SELECT OBJECT_ID,OBJECT_TYPE,STATUS FROM ALL_OBJECTS WHERE 1=2;

1、建立一个触发器,当表被删除一条记录时,把被删除记录写到日志表中去

CREATE OR REPLACE TRIGGER TRI_T_TRIGGER_DEL

BEFORE DELETE

ON T_TRIGGER

FOR EACH ROW

  BEGIN

    INSERT INTO T_TRIGGER_LOG(OBJECT_ID,OBJECT_TYPE,STATUS)

    VALUES(:OLD.OBJECT_ID,:OLD.OBJECT_TYPE,:OLD.STATUS);

  END;

SELECT * FROM T_TRIGGER;

DELETE T_TRIGGER WHERE OBJECT_ID=20;

SELECT * FROM T_TRIGGER_LOG;

--DROP TRIGGER TRI_T_TRIGGER_DEL;

2、限制对T_TRIGGER表修改,即不允许在非工作时间修改T_TRIGGER表

CREATE OR REPLACE TRIGGER TRI_T_TRIGGER_DEL

BEFORE INSERT OR DELETE OR UPDATE

ON T_TRIGGER

BEGIN

  IF(TO_CHAR(SYSDATE,'DAY') IN ('星期六','星期日')) OR (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '8:30' AND '16:00') THEN

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

  END IF;

END;

INSERT INTO T_TRIGGER VALUES(0,'TRIGGER','VALID');

DELETE T_TRIGGER WHERE OBJECT_ID=3;

UPDATE T_TRIGGER SET OBJECT_ID=20 WHERE OBJECT_ID=3;

3、有限定条件的触发器

CREATE OR REPLACE TRIGGER TRI_T_TRIGGER_DEL

BEFORE UPDATE OF OBJECT_ID OR DELETE

ON T_TRIGGER

FOR EACH ROW

WHEN (OLD.OBJECT_ID =3)

BEGIN

  CASE

    WHEN UPDATING('OBJECT_ID') THEN

      IF :NEW.OBJECT_ID < :OLD.OBJECT_ID THEN

        RAISE_APPLICATION_ERROR(-20001,'不能更改为更小值');

      END IF;

    WHEN DELETING THEN

      RAISE_APPLICATION_ERROR(-20001,'不能删除OBJECT_ID为3的数据');

    END CASE;

END;

UPDATE T_TRIGGER SET OBJECT_ID=2 WHERE OBJECT_ID=3;

DELETE T_TRIGGER WHERE OBJECT_ID=3;

4、利用触发器实现级联更新

CREATE TABLE T_USER(ID NUMBER,NAME VARCHAR2(10),ADDR NUMBER);

INSERT INTO T_USER VALUES(1,'1111',1);

INSERT INTO T_USER VALUES(2,'2222',2);

CREATE TABLE T_ADDR(ID NUMBER,ADDR_DESC VARCHAR2(20));

INSERT INTO T_ADDR VALUES(1,'JKLJKJKJ');

INSERT INTO T_ADDR VALUES(2,'JKLJKLJLK');

DROP TRIGGER TRI_T_TRIGGER_DEL;

CREATE OR REPLACE TRIGGER TRI_T_TRIGGER_DEL

AFTER UPDATE OF ID

ON T_ADDR

FOR EACH ROW

BEGIN

  UPDATE T_USER SET ID = :NEW.ID WHERE ID = :OLD.ID;

END;

UPDATE T_ADDR SET ID = 3 WHERE ID = 2;

SELECT * FROM T_USER;

5、在触发器中调用过程(注意:触发器在执行过程中会将表锁住,所以调用的存储过程不能和触发器操作同一张表)

CREATE OR REPLACE PROCEDURE PRO_TRIGGER

IS

BEGIN

  INSERT INTO T_ADDR VALUES(10,'1010010');

END PRO_TRIGGER;

DROP TRIGGER TRI_T_TRIGGER_DEL;

CREATE OR REPLACE TRIGGER TRI_T_TRIGGER_DEL

AFTER UPDATE OF NAME ON T_USER

FOR EACH ROW

BEGIN

  PRO_TRIGGER;

END;

UPDATE T_USER SET NAME='FFFF' WHERE ID=1;

SELECT * FROM T_ADDR;

INSTEAD OF触发器

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

不能指定BEFORE或AFTER选项

FOR EACH ROW是可选的

系统触发器

ORACLE 10G提供的系统事件触发器可以在DDL或数据库系统上被触发.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25285600/viewspace-772443/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25285600/viewspace-772443/