Oracle Trigger

 

Oracle 资料

Oracle Trigger

1、这是oracle的规定，不能对执行触发器的表进行操作。  

可以对new.xxx进行操作（行级触发器）。其中在Before Update触发器可以访问New/Old行的访问（读取，赋值）。

对于oracle行级触发器(for   each   row)，不能对本表做任何操作，包括读取

原则：  

  在before   insert触发器中，可以实现对本表的访问；  

  在after   insert触发器中，不能实现对本表的访问；  

  在before/after   update/delete触发器中，都不能实现对本表的访问  

  其实原因很简单，就是为了防止脏读  

2、写oracle行级触发器时,不能操作本表,报"表 *** 发生了变化，触发器/函数不能读"的错误的解决办法

原因已经很明显了就是行级的触发器代码中不能操作该表,包括select,是挺郁闷的

当然解决方法就是要根据原因了,正因为限定了行级触发器的操作,只能选择表级的触发器了,但是在表级的触发器又不能获得:new和:old的值,那就只能采取两种触发器并用的方法了,并且还要包或者临时表加以辅助.

首先在行级触发器中将所需的,:new或者:old的值,写到包或者临时表中

然后在表级触发器中处理包或者临时表中已经写入的数据,操作成功后可以按照需求再删除临时表的数据.

3、 ORACLE 触发器

ORACLE产生数据库触发器的语法为：

create [or replace] trigger 触发器名 触发时间 触发事件

on 表名

[for each row]

pl/sql 语句

其中：

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

触发时间：指明触发器何时执行，该值可取：

 

before---表示在数据库动作之前触发器执行；

 

after---表示在数据库动作之后出发器执行。

 

触发事件：指明哪些数据库动作会触发此触发器：

 

insert：数据库插入会触发此触发器；

 

update：数据库修改会触发此触发器；

 

delete：数据库删除会触发此触发器。

 

表 名：数据库触发器所在的表。

 

for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

 

根据以上的条件，并结合网络上的一些资料，做了一个TRIGGER，具体如下：

 

create or replace trigger Trg_Dept

 

  after insert or update or delete on dept

 

  referencing old as old_value new as new_value

 

  for each row

 

declare

 

  -- local variables here

 

begin

 

    if inserting then

 

    insert into dept_test(dept_id,dept_no,create_date) values (:new_value.dept_id,:new_value.dept_no,SYSDATE);

 

    elsif updating then

 

    update dept_test set dept_no=:new_value.dept_no,revise_date=sysdate where dept_id=:new_value.dept_id;

 

    elsif deleting then

 

    delete from dept_test where dept_id=:old_value.dept_id;

 

    end if;

 

end trg_dept;

 

*******************************************************

 

触发器中不能对触发表进行操作，否则会报错：触发器/函数不能读它

 

下面是错误的例子：

 

CREATE OR REPLACE TRIGGER TD_ITMS_RADIUS_PPPOE_SYNC  AFTER INSERT  on ITMS_ORDER_SERVICE_TEMP  for each row

 

DECLARE

TYPE PppoeRecordType is RECORD(

 account VARCHAR(256)

);

TYPE PppoeCurType IS REF CURSOR;

PPPOE VARCHAR2(256);

pppoeRec PppoeRecordType;

pppoeCur PppoeCurType;

 

BEGIN

 OPEN pppoeCur FOR

 SELECT ITMS_ORDER_SERVICE_TEMP.ARGS_VALUE_NEW FROM ITMS_ORDER_SERVICE_TEMP WHERE ITMS_ORDER_SERVICE_TEMP.ORDER_ID = :new.ORDER_ID

  AND ITMS_ORDER_SERVICE_TEMP.ARGS_NAME='PPPUser'

  AND ITMS_ORDER_SERVICE_TEMP.SERVICE='GWOrderSync';

 

 FETCH pppoeCur INTO pppoeRec;

   

 IF pppoeCur%FOUND then

  IF pppoeRec.account IS NOT NULL THEN

  PPPOE := pppoeRec.account;

  INSERT INTO RADIUS_PPPOE_SYNC (AD_ACCOUNT, DONE_FLAG) VALUES(PPPOE,'0');

  END IF;

 END IF;

 

 CLOSE pppoeCur;

 

END;

理论知识：

Basic Trigger Syntax

Below is the syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):

CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]]

<trigger_body>

Some important points to note:

You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only available for views; typically they are used to implement view updates.)

You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:

... INSERT ON R ...

... INSERT OR DELETE OR UPDATE ON R ...

... UPDATE OF A, B OR INSERT ON R ...

If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.

Only for row-level triggers:

The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! See example below.

The REFERENCING clause can be used to assign aliases to the variables NEW and OLD.

A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger is fired for each row.

<trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <trigger_body> include:

You cannot modify the same relation whose modification is the event triggering the trigger.

You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

--------------------------------------------------------------------------

Trigger Example

We illustrate Oracle's syntax for creating a trigger through an example based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));

CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:

CREATE TRIGGER trig1

AFTER INSERT ON T4

REFERENCING NEW AS newRow

FOR EACH ROW

WHEN (newRow.a <= 10)

BEGIN

INSERT INTO T5 VALUES(:newRow.b, :newRow.a);

END trig1;

.

run;

Notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the trigger to execute.

--------------------------------------------------------------------------

Displaying Trigger Definition Errors

As for PL/SQL procedures, if you get a message

Warning: Trigger created with compilation errors.

you can see the error messages by typing

show errors trigger <trigger_name>;

Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error. Note that the reported line numbers where the errors occur are not accurate.

--------------------------------------------------------------------------

Viewing Defined Triggers

To view a list of all defined triggers, use:

select trigger_name from user_triggers;

For more details on a particular trigger:

select trigger_type, triggering_event, table_name, referencing_names, trigger_body

from user_triggers

where trigger_name = '<trigger_name>';

--------------------------------------------------------------------------

Dropping Triggers

To drop a trigger:

drop trigger <trigger_name>;

--------------------------------------------------------------------------

Disabling Triggers

To disable or enable a trigger:

alter trigger <trigger_name> {disable|enable};

 

--------------------------------------------------------------------------

Aborting Triggers with Error

Triggers can often be used to enforce contraints. The WHEN clause or body of the trigger can check for the violation of certain conditions and signal an error accordingly using the Oracle built-in function RAISE_APPLICATION_ERROR. The action that activated the trigger (insert, update, or delete) would be aborted. For example, the following trigger enforces the constraint Person.age >= 0:

create table Person (age int);

CREATE TRIGGER PersonCheckAge

AFTER INSERT OR UPDATE OF age ON Person

FOR EACH ROW

BEGIN

IF (:new.age < 0) THEN

RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed');

END IF;

END;

.

RUN;

If we attempted to execute the insertion:

insert into Person values (-3);

we would get the error message:

ERROR at line 1:

ORA-20000: no negative age allowed

ORA-06512: at "MYNAME.PERSONCHECKAGE", line 3

ORA-04088: error during execution of trigger 'MYNAME.PERSONCHECKAGE'

and nothing would be inserted. In general, the effects of both the trigger and the triggering statement are rolled back.

 

--------------------------------------------------------------------------

Mutating Table Errors

Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors:

A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.)

A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.

 

--------------------------------------------------------------------------

例子1

create or replace trigger TR_AAI01

before delete or update or insert on AAI01

for each row

declare

-- local variables here

number_seq number;

number_rowid number;

begin

if updating then

select seq_tradenum.nextval into number_seq from dual;

:new.EBZ008 :=number_seq;

end if;

if deleting then

insert into EBZ08(EBZ027,AAC000,EBZ008,EBZ009) values ('AAI01',:old.AAI000,:old.EBZ008,:old.EBZ009);

end if;

if inserting then

select seq_rowid.nextval into number_rowid from dual;

select seq_tradenum.nextval into number_seq from dual;

:new.EBZ008 :=number_seq;

:new.EBZ009 := to_char(number_rowid);

end if;

end TR_AAI01;

那么如何截获触发触发起的sql语句呢？itpub上 ryuxy 是这样处理的（原文http://www.itpub.net/701184.html）。

CREATE OR REPLACE TRIGGER TRG_LOG_SQL

BEFORE INSERT

ON MYTABLE

DECLARE

n NUMBER;

stmt varchar2(4000);

sql_text ora_name_list_t;

BEGIN

n := ora_sql_txt(sql_text);

FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

INSERT INTO DDL_LOG(DDL_TIME, DDL_USER, DDL_HOST, DDL_IP, DDL_SQL)

VALUES(SYSDATE,USER,SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV','IP_ADDRESS'),stmt);

EXCEPTION

WHEN OTHERS THEN

RAISE;-- Consider logging the error and then re-raise

END ;