Re: HELP:How to create the trigger?

Hi Violin
A row-level trigger cannot 'see' the table on which it is defined because the table is changing ('mutating'). The correct solution for your problem would be a row-level trigger which inserts the deleted rows' primary key into a PL/SQL table in a package and a second statement-level trigger which uses the information stored in the PL/SQL table and checks the original table.

Hope this helps

Violin wrote:
>
> Hello,
> I have a BIG....BIG problem,and I hope someone could help me!!!
> I have a tables:VOUFD
> I want to create a trigger:BEFORE DELETE ON VOUFD FOR EACH ROW
>
> VOUFD Primmary Key
> ----------------------------------------------------------
> vou_no CHAR(8) \/
> ord_no CHAR(8)
> qty NUMBER
>
> If I delete a row from VOUFD,
> I want to check if there is any other row where ord_no = :old.ord_no
> So I have the script like this:
>
> CREATE OR REPLACE TRIGGER DEL_VOUFD
> BEFORE DELETE
> ON VOUFD
> FOR EACH ROW
> DECLARE
> temp CHAR(8);
> CURSOR C1 IS
> SELECT VOU_NO FROM VOUFD WHERE ORD_NO = :old.ord_no;
>
> BEGIN
> temp := '';
> OPEN C1;
> FETCH C1 INTO temp;
> IF C1%FOUND THEN
> temp := '1';
> ELSE
> temp := '2';
> END IF;
> ::::::::::::::::::::::::::::
> END DEL_VOUFD;
>
> And the trigger is created successfully.
> But when I do "delete" command:
> DELETE FROM VOUFD WHERE VOU_NO = '000001';
> An error occurs:
> ORA-04091: table SCOTT.VOUFD is mutating,
> trigger/function may not see it
> ORA-06512: at "SCOTT.DEL_VOUFD", line 4
> ORA-06512: at "SCOTT.DEL_VOUFD", line 7
> ORA-04088: error during execution of trigger 'SCOTT.DEL_VOUFD'
>
> SOS:How could I sove the problem??Could anyone help me?
> Please give me some suggestions.
> and please Cc to : violin.hsiao_at_mail.pouchen.com.tw
> Best Regards. :-)
>
> Violin.
Received on Wed Sep 16 1998 - 08:35:43 CDT