Re: Trigger problem

Anurag Varma wrote:
> "Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message
news:1109866982.952906.119680_at_o13g2000cwo.googlegroups.com...
> >
> > Jim Kennedy wrote:
> > <snip>
> > > cascade). Else you would have to use a mutating trigger wor
around
> > see
> > > asktom.oracle.com.
> > > Jim
> >
> > Since I mentioned the autonomous_transaction... here is an example
to
> > go with it.
> >
> > SQL> create table foo_a (test number);
> >
> > Table created
> >
> > SQL> create table foo_b (test number);
> >
> > Table created
> >
> > SQL> insert into foo_a values (1);
> >
> > 1 row inserted
> >
> > SQL> insert into foo_a values (2);
> >
> > 1 row inserted
> >
> > SQL> insert into foo_b values (1);
> >
> > 1 row inserted
> >
> > SQL> insert into foo_b values (2);
> >
> > 1 row inserted
> >
> > SQL> commit;
> >
> > Commit complete
> >
> > SQL> create or replace procedure foo_a_proc (
> > 2 test_ in number)
> > 3 is
> > 4 pragma autonomous_transaction;
> > 5 count_ number;
> > 6 begin
> > 7 select count(*)
> > 8 into count_
> > 9 from foo_a where test = test_;
> > 10 -- Use 1 since no commits done. This means last row deleted
> > 11 if (nvl(test_, 0) = 1) then
>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^
> maybe this should be "if(nvl(count_,0)=1) then"
>
>
> > 12 delete from foo_b
> > 13 where test = test_;
> > 14 commit;
> > 15 end if;
> > 16 end foo_a_proc;
> > 17 /
> >
> > Procedure created
> >
> > SQL> create or replace trigger foo_a_trg
> > 2 after delete
> > 3 on foo_a
> > 4 for each row
> > 5 begin
> > 6 foo_a_proc(:old.test);
> > 7 end foo_a_trg;
> > 8 /
> >
> > Trigger created
> >
> > SQL> select * from foo_a;
> >
> > TEST
> > ----------
> > 1
> > 2
> >
> > SQL> select * from foo_b;
> >
> > TEST
> > ----------
> > 1
> > 2
> >
> > SQL> delete from foo_a where test = 1;
> >
> > 1 row deleted
> >
> > SQL> commit;
> >
> > Commit complete
> >
> > SQL> select * from foo_a;
> >
> > TEST
> > ----------
> > 2
> >
> > SQL> select * from foo_b;
> >
> > TEST
> > ----------
> > 2
> >
> > Regards
> > /Rauf
> >
>
> Try your example by inserting two rows in foo_a of values 1.
> The rows from foo_b will not be deleted.
>
> Then try the example by inserting two rows in foo_a with value 1
> and then from two different sessions delete one row each and then
> commit in the two sessions.
> The rows from foo_b will not be deleted.
>
> Your example will work only in certain conditions. Probably not what
the OP
> wants...
>
> Tom's example here might be better.... (link might wrap)
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:17301636876527644131::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1500805205887
>
> Anurag

Thanks for the correction... this would have worked only in one situation... which I posted :>)

After another look at it and rewriting it in few different ways to use autonomous_transaction... I kinda got it to work but the solution became more complex then the actual issue at hand. The exercise also re-enforced my old dislike for triggers specially in these situations.

Going by simple solution for simple problems logic... I would agree with Holger and just use the stored procedure and take trigger out of the equation.

Regards
/Rauf Received on Fri Mar 04 2005 - 19:07:13 CST