Re: Trigger problem

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 03 Mar 2005 23:51:32 GMT
Message-ID: <8qNVd.36685$vK5.969@twister.nyroc.rr.com>

"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 Received on Thu Mar 03 2005 - 17:51:32 CST