Re: Trigger problem

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 3 Mar 2005 08:23:03 -0800
Message-ID: <1109866982.952906.119680@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
 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 Received on Thu Mar 03 2005 - 10:23:03 CST