RE: Trigger procedure problem

Thanks for your replies,

   I want just check if the new value for epoch column is already exists if so exit else I do the insertion. TIA Kader

• Gunawan Yuwono <gunawan.yuwono_at_webbox.com> wrote:
  >
  > Could you explain in more detail what you want to
  > accomplish
  > from this trigger?
  >
  > If the stored procedure is executed inside the
  > trigger, won't
  > it try to fire the trigger again since the stored
  > procedure itself
  > is trying to insert into the table? Or am I missing
  > something
  > here?
  >
  >
  >
  > Gunawan Yuwono
  > Oracle DBA
  > Kansas City, MO
  >
  > >--- Original Message ---
  > >From: Kader Ben <kaderb_at_yahoo.com>
  > >To: Multiple recipients of list ORACLE-L
  > <ORACLE-L_at_fatcity.com>
  > >Date: 9/28/00 4:00:43 AM
  > >
  >
  > >Hi gurus,
  > > Thanks for ypur replies. As suggested from most
  > of
  > >you. To workaroud my mutating table/trigger. I
  > create
  > >a trigger to execute a procedure that do an insert.
  > >But I run into an onther error (ORA-01403: no data
  > >found).
  > >
  > >Thank you for your help.
  > >
  > >--------------------------
  > >
  > >SQL> create or replace procedure
  > PROC_WEB_ACTU_STAT(
  > > x WEBFIN_ACTU_STAT.id%TYPE, y
  > >WEB_ACTU_STAT.epoch%TYPE)
  > >AS
  > >
  > >BEGUIN
  > >
  > > insert into WEBFIN_ACTU_STAT(ID, EPOCH) VALUEs(x,
  > >y);
  > > commit;
  > >END;
  > >/
  > >-----------------------
  > >
  > >SQL> CREATE OR REPLACE TRIGGER TRG_WEB_ACTU_STAT
  > >
  > > BEFORE INSERT OR UPDATE ON TFIN.WEB_ACTU_STAT
  > > FOR EACH ROW
  > > DECLARE
  > > epoch_stat tfin.webfin_actu_stat.epoch%TYPE;
  > > BEGIN
  > > SELECT epoch INTO epoch_stat FROM
  > TFIN.WEB_ACTU_STAT
  > > WHERE TFIN.WEB_ACTU_STAT.epoch =
  > :new.epoch;
  > > IF epoch_stat IS NULL
  > > THEN
  > > PROC_WEBFIN_ACTU_STAT(:new.id, :new.epoch);
  > > END IF;
  > > END;
  > >
  > > /
  > >
  > >----------------------
  > >
  > >SQL> desc TFIN.WEB_ACTU_STAT
  > >
  > > Name Null? Type
  > > ------------------------------- -------- ----
  > > ID NOT NULL
  > NUMBER(10)
  > > EPOCH NOT NULL
  > VARCHAR2(19)
  > > COMPTEUR NUMBER(7)
  > >
  > >----------------------------------------------------
  > >
  > >SQL> insert into WEB_ACTU_STAT(ID, EPOCH) VALUES(1,
  > >'1452');
  > >
  > >insert into WEBFIN_ACTU_STAT(ID, EPOCH) VALUES(1,
  > >'1452')
  > > *
  > >ERROR at line 1:
  > >
  > >ORA-01403: no data found
  > >
  > >-------------------------------------------
  > >
  > >__________________________________________________
  > >Do You Yahoo!?
  > >Yahoo! Photos - 35mm Quality Prints, Now Get 15
  > Free!
  > >http://photos.yahoo.com/
  > >--
  > >Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > >--
  > >Author: Kader Ben
  > > INET: kaderb_at_yahoo.com
  > >
  > >Fat City Network Services -- (858) 538-5051
  > FAX: (858) 538-5051
  > >San Diego, California -- Public Internet
  > access / Mailing
  > Lists
  > >--------------------------------------------------------------------
  > >To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > >to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru')
  > and in
  > >the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > >(or the name of mailing list you want to be removed
  > from).
  > You may
  > >also send the HELP command for other information
  > (like subscribing).
  > >
  > >
  > ----------------
  > Sent from a WebBox - http://www.webbox.com
  > FREE Web based Email, Files, Bookmarks, Calendar,
  > People and
  > Great Ways to Share them with Others!
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Gunawan Yuwono
  > INET: gunawan.yuwono_at_webbox.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).