| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP:How to create the trigger?
A copy of this was sent to violin.hsiao_at_mail.pouchen.com.tw (Violin)
(if that email address didn't require changing)
On Wed, 16 Sep 1998 01:36:15 GMT, you 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.
The way to do this is to defer reading the table until after all of the row level changes have been made. Below is an example. We use a package to maintain a state across the triggers. the first trigger, a BEFORE trigger, simply resets the package state to some know state. the second trigger collects all of the ord_nos affected by the delete into a table. The third trigger contains all of the logic you want to perform for the affected rows. You will loop over the entries in the pl/sql table.
SQL> create table voufd ( vou_no char(8), ord_no char(8), qty number ); Table created.
SQL> create or replace package voufd_pkg 2 as
3 type ord_noArray is table of voufd.ord_no%type index by binary_integer; 4 ord_nos ord_noArray; 5 empty_ord_nos ord_noArray;6 end;
SQL> create or replace trigger emp_bu
2 before update on emp
3 begin
4 voufd_pkg.ord_nos := voufd_pkg.empty_ord_nos;
5 end;
6 /
Trigger created.
SQL> create or replace trigger voufd_adfer
2 after delete on voufd
3 for each row
4 begin
5 voufd_pkg.ord_nos( voufd_pkg.ord_nos.count+1 ) := :old.ord_no;
6 end;
7 /
Trigger created.
SQL> create or replace trigger voufd_ad
2 after delete on voufd
3 declare
4 cnt number;
5 begin
6 for i in 1 .. voufd_pkg.ord_nos.count loop 7 select count(*) into cnt 8 from voufd 9 where ord_no = voufd_pkg.ord_nos(i); 10 10 dbms_output.put_line( voufd_pkg.ord_nos(i) || ' cnt = ' || cnt ); 11 end loop; 12 voufd_pkg.ord_nos := voufd_pkg.empty_ord_nos;13 end;
SQL> insert into voufd values ( '1' ,'1', 5 ); SQL> insert into voufd values ( '2' ,'1', 5 ); SQL> insert into voufd values ( '3' ,'2', 5 ); SQL> delete from voufd where vou_no = 1; 1 cnt = 1
1 row deleted.
SQL> delete from voufd where vou_no = 3; 2 cnt = 0
1 row deleted.
So, the first delete shows we can detect the presence of another ord_no with the same value in the table and the second shows what happens when there are no matches...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Sep 16 1998 - 09:03:26 CDT
 |
 |