SEO

Dear Faculties,
I have a problem in trigger.There is a table CATEGORY_TRANS,

SQL> desc CATEGORY_TRANS
Name Null? Type
-------------------------- -------- -----------
BLOG_ID NUMBER(10)
CATEGORY_ID NUMBER(10)

..Now before deleting any row I need to check the number of BLOG_ID
of same.Please suuggest me.
Regards
Arijit Chatterjee

arijitchatterjee123@yahoo.co.in wrote:

> .Now before deleting any row I need to check the number of BLOG_ID
> of same.Please suuggest me.

Which kind of check you should perform and what is the problem you are
experiencing?

Kind regards,

--
Cris Carampa (cris119@operamail.com)

"Torna il re. Prosegue l'attesa per fante e cavallo."
(da "Umanità Nova" n. 14 del 21 aprile 2002)

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

arijitchatterjee123@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...A:469621337269

I hope you get your problem solved.


David Fitzjarrell