Re: Filter Data on Before Delete Trigger...

From: <fitzjarrell_at_cox.net>
Date: 2 Mar 2005 10:54:47 -0800
Message-ID: <1109789687.776217.47890@l41g2000cwc.googlegroups.com>

arijitchatterjee123_at_yahoo.co.in wrote:
> Thanks Cris,Thanks for your response I was trying like this
> ...
> CREATE OR REPLACE TRIGGER CATEGORY_TRANS_DATA_DELETE
> BEFORE DELETE ON CATEGORY_TRANS
> FOR EACH ROW
> BEGIN
> DECLARE
> TOTAL_COUNT NUMBER(10,0);
> RESPONSE CHAR(2);
> BEGIN
> SELECT COUNT(*) INTO TOTAL_COUNT FROM CATEGORY_TRANS WHERE
> BLOG_ID=:OLD.BLOG_ID;
> IF TOTAL_COUNT = 1 THEN
> DBMS_OUTPUT.PUT_LINE('SINGLE RECORD FOUND...DO YOU WANT TO
> DELETE???? ...Y/N');
> END IF;
> END;
> END;
> ..
> And I made it.
> Thanks once again
> Regards
> Arijit Chatterjee

I'm surprised this trigger works for you, as it doesn't for me:

SQL> CREATE TABLE CATEGORY_TRANS(

  2  BLOG_ID	     number(10),
  3  CATEGORY_ID     number(10));

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER CATEGORY_TRANS_DATA_DELETE

  2 BEFORE DELETE ON CATEGORY_TRANS
  3 FOR EACH ROW
  4 BEGIN
  5 DECLARE

  6  	     TOTAL_COUNT NUMBER(10,0);
  7  	     RESPONSE CHAR(2);
  8  	     BEGIN
  9  		     SELECT COUNT(*) INTO TOTAL_COUNT FROM CATEGORY_TRANS WHERE
 10  BLOG_ID=:OLD.BLOG_ID;
 11  		     IF TOTAL_COUNT = 1 THEN
 12  			     DBMS_OUTPUT.PUT_LINE('SINGLE RECORD FOUND...DO YOU WANT TO
 13  DELETE???? ...Y/N');
 14  		     END IF;
 15  	     END;

 16 END;
 17 /

Trigger created.

SQL>
SQL> begin

  2  	     for x in 1..100 loop
  3  		     for y in 1..4 loop
  4  			     insert into category_trans values (x,y);
  5  		     end loop;
  6  	     end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> delete from category_trans where blog_id = 1;
delete from category_trans where blog_id = 1
            *

ERROR at line 1:
ORA-04091: table SCOTT.CATEGORY_TRANS is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.CATEGORY_TRANS_DATA_DELETE", line 6 ORA-04088: error during execution of trigger 'SCOTT.CATEGORY_TRANS_DATA_DELETE' SQL> Simply because you get 'Trigger created.' in no way indicates the trigger will function properly, as you can see from the above example. Had you perused asktom.oracle.com you'd have discovered you need a stored procedure to query the same table your trigger is created against, to prevent such 'mutating table' errors. Hopefully this link will help, since Tom Kyte can explain it far better than I can:

http://asktom.oracle.com/pls/ask/f?p=4950:8:14829588873694980657::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:469621337269

I hope you get your problem solved.

David Fitzjarrell Received on Wed Mar 02 2005 - 12:54:47 CST