triggers and functions ???

From: Isma <Ismael_at_NOSPAMiballo.com>
Date: Wed, 9 Jan 2002 17:43:11 +0100
Message-ID: <a1hs16$i8e$1_at_sunnews.cern.ch>

HI,
[Quoted] 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 - 17:43:11 CET