Re: triggers and functions ???

Your trigger is trying to modify the table on which it has been placed.

Your after insert trigger is attempting another insert which fires the trigger again which .... etc.

Daniel A. Morgan

Isma wrote:

> HI,
> I'm new to trigger and functions stuff in Oracle.
> I write a function warr_date which returns me a date and this below trigger.
> The trigger is created but when I want to use insert like this for exple :
>
> update infoglob set warr_date = warr_date('2JXE005083');
> I've got this error.
> However, I made an update on the column warr_date and not arrival. Why does
> it trigger ?
> And I noticed that I can update with constants like :
> update infoglob set warr_date = '12-NOV-2000';
>
> ERROR at line 1:
> ORA-04091: table is mutating, trigger/function may not see it
> ORA-06512: at line 1
>
> CREATE OR REPLACE TRIGGER trig_warr_date
> AFTER INSERT OR UPDATE OF ARRIVAL ON INFOGLOB
> REFERENCING NEW AS n
> FOR EACH ROW
> BEGIN
> IF INSERTING THEN
> INSERT INTO INFOGLOB(warr_date) VALUES (I.warr_date(:n.serial_n));
> END IF;
> IF UPDATING('ARRIVAL') THEN
> UPDATE INFOGLOB SET INFOGLOB.warr_date = I.warr_date(serial_n) WHERE
> INFOGLOB.serial_n = :n.serial_n;
> END IF;
> END;
> /
>
> The function is warr_date and it works well separately :
>
> CREATE OR REPLACE FUNCTION warr_date (f_serial_n VARCHAR2)
> RETURN DATE
> IS
> r_serial_n VARCHAR2(20) ;
> r_sn_rep VARCHAR2(20) ;
> i_arrival DATE;
> BEGIN
>
> BEGIN
> SELECT serial_n,sn_rep
> INTO r_serial_n,r_sn_rep
> FROM RMA_R
> WHERE serial_n = f_serial_n;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN NULL;
> END;
>
> BEGIN
> SELECT arrival
> INTO i_arrival
> FROM INFOGLOB
> WHERE serial_n= f_serial_n;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN RETURN(NULL);
> END;
>
> IF r_serial_n IS NULL OR r_serial_n=r_sn_rep THEN
> RETURN(i_arrival);
> ELSE
> RETURN(r_warr_date(f_serial_n));
> END IF;
>
> END;
> /
Received on Wed Jan 09 2002 - 13:27:44 CET