RE: Trigger procedure problem

Many thanks Diana for your helpful course!

Kader

• "Thapliyal, Deepak" <DThapliyal_at_ea.com> wrote:
  > very good explanation diana
  >
  > -----Original Message-----
  > Sent: Thursday, September 28, 2000 4:06 PM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > OK! Good to know. I know I was looking at the code
  > and wondering "What the
  > heck...?"
  >
  > So, first let me explain what some of your problems
  > stem from:
  > 1. You can't look at or touch records in the table
  > that the trigger is on in
  > a row trigger. This is because the rows are
  > changing, and in an uncertain
  > state. Oracle cannot guarantee that what you are
  > seeing is consistent,
  > hence the "mutating tables" error. Even if you call
  > a procedure in that
  > trigger, the procedure can't look at or touch the
  > records. And there is no
  > way that you can perform an insert on the mutating
  > table from a "for each
  > row" type of trigger.
  > 2. When you do a "Select Into" statement, Oracle
  > will complain if it didn't
  > find something. The best way to avoid the complaint
  > is use an explicit
  > rather than implicit cursor. Then, you can check
  > the cursor attribute
  > %notfound.
  > 3. You actually haven't encountered this problem
  > yet, but you can't do
  > "commit" or "rollback" statements from within a
  > trigger either. That would
  > subvert Oracle's transaction handling.
  > 4. There is no way to "exit" from an insertion that
  > is already in progress
  > without throwing an error. So, you will have to
  > handle the error somehow in
  > your application code -- you could just ignore it.
  > ;-) The other way would
  > be to have a procedure called in the "after table"
  > trigger that actually
  > goes and deletes the record you just inserted.
  >
  > That being said, you need to do something like this:
  > (The examples given
  > previously were very good, but let's just reiterate)
  >
  > create or replace package dupCheckPkg is
  > /*
  > * This structure is populated by the before insert
  > or update
  > trigger,
  > * then is processed in the after insert or update
  > trigger for the
  > entire
  > * table. This avoids the mutating tables error.
  > */
  > type statTabType is table of
  > tfin.web_actu_stat.epoch%type index by
  > binary_integer;
  > statTab statTabType;
  >
  > /*
  > * checkDuplicates checks the web_actu_stat table
  > for existing rows
  > with the same
  > * epoch as the rows awaiting insertion. If an
  > there is an existing
  > row, return
  > * TRUE.
  > */
  > function checkDuplicates return boolean;
  > end dupCheckPkg;
  > /
  >
  > create or replace package body dupCheckPkg is
  > function checkDuplicates return boolean is
  > -- This is where you use an explicit cursor
  > cursor do_check (p_epoch in varchar2) is
  > select 'X'
  > from tfin.web_actu_stat
  > where epoch = p_epoch;
  > dummy varchar2(1);
  > i binary_integer;
  > begin
  > for i in 1..nvl(statTab.LAST,0) loop
  > open do_check(statTab(i));
  > fetch do_check into dummy;
  > close do_check;
  > end loop;
  >
  > statTab.DELETE;
  > if (dummy = 'X') then
  > return TRUE;
  > else
  > return FALSE;
  > end if;
  > end checkDuplicates;
  > end dupCheckPkg;
  > /
  >
  > create or replace biur_web_actu_stat
  > before insert or update on tfin.web_actu_stat
  > for each row
  > begin
  > -- Here, add the epoch to the list in the package.
  > You probably
  > only care
  > -- to do it in updates when the value has actually
  > changed.
  > --
  > if (INSERTING or :old.epoch != :new.epoch) then
  >
  > dupCheckPkg.statTab(nvl(dupCheckPkg.statTab.LAST,0)
  > + 1) :=
  > :new.epoch;
  > end if;
  > end biur_web_actu_stat;
  > /
  >
  > create or replace trigger aiu_web_actu_stat
  > after insert or update on tfin.web_actu_stat
  > declare
  > duplicate boolean := FALSE;
  > begin
  > duplicate := dupCheckPkg.checkDuplicates;
  >
  > if duplicate then
  > raise_application_error(-20102, 'This epoch
  > already
  > exists.');
  > end if;
  > end aiu_web_actu_stat;
  > /
  >
  > As per usual, this is untested code (although I took
  > it from a similar check
  > I do, so it
  > should work barring any typing mistakes). I hope
  > this has increased your
  > understanding of triggers! ;-)
  >
  > Regards,
  >
  > Diana
  >
  >
  > -----Original Message-----
  > Sent: Thursday, September 28, 2000 11:31 AM
  > To: Multiple recipients of list ORACLE-L
  >
  >
  > 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