--0-327254586-970082610=:11462
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Oracle does not allow you to perform DML statements on
the target source table in a row level trigger.
You can either use a temp table to get modified data
from or write a package you''ll find in attachement.
- Kader Ben <kaderb_at_yahoo.com> wrote:
> Hi friend,
> I have created trigger (see code below) to
> insert
> some values into table. But this generate ORA-4091
> error (mutating table).
> Could someone suggest me a workaround?
> Thank you in advance for your help.
>
> Kader
>
> -------------------
> CREATE OR REPLACE TRIGGER "TFIN"."TRG_WEB_ACTU_STAT"
>
> AFTER INSERT ON "TFIN"."WEB_ACTU_STAT" FOR EACH ROW
> declare
> epoch_stat webfin_actu_stat.epoch%TYPE;
> comment varchar2(20);
> begin
> select epoch into epoch_stat from web_actu_stat
> where
> epoch= :old.epoch;
> if epoch_stat = ' ' THEN
> INSERT INTO WEB_ACTU_STAT (ID, EPOCH) VALUES
> (:NEW.ID, ':New.epch');
> END IF;
>
> end;
> /
> ------------------------------
>
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo!
> Messenger.
> http://im.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Kader Ben
> INET: kaderb_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
--0-327254586-970082610=:11462
Content-Type: text/plain; name="Just_an_example.txt"
Content-Description: Just_an_example.txt
Content-Disposition: inline; filename="Just_an_example.txt"
/*
**
**
** Background and underlying assumptions:
**
** 1. Table 'po_header' contains Purchase Order (PO) header records.
** Each record is identified by the column 'po_no' (Purchase Order Number),
** the type of Purchase Order is indicated by a column 'po_type',
** and a 'po_status' column indicates the status of the Purchase Order.
**
** 2. Some special processing is required after a particular type of
** Purchase Order is approved, and someone has decided to implement that
** using database triggers rather than by some means which might be more
** sensible - such as via a stored procedure. The special processing causes
** a mutating table error if performed in the row-level trigger.
**
** 3. The package PO_PKG contains procedures for operating on Purchase Orders,
** and a private PL/SQL table which is used by the row-level database trigger to
** register POs for attention by the statement-level trigger.
**
*/
--
Create or Replace Package PO_PKG is
--
-- Package Specification (incomplete)
--
PO_STATUS_APPROVED constant po_header.po_status%type = 'APPROVED';
PO_TYPE_PREVENTIVE_MAINTENANCE constant po_header.po_type%type = 'PM';
--
type po_rectype is record
(Pono_f po_header.po_no%type);
--
type PONO_Table_type is table of po_rectype index by binary_integer;
--
--...
-- Among other things which are missing from this package spec are
-- the declarations of the procedures!
--...
end PO_PKG;
/
Create or replace
Package body PO_PKG is
--
-- Package Body (incomplete)
--
-- PRIVATE PACKAGE VARIABLES
--
-- Table of Registered PM PONOs for use by database triggers.
--
PMPOT PONO_Table_Type;
PMPOT_index binary_integer := 0;
--
/*
**********************************************************
* PROCEDURE:
* Register_Approved_PO
*
* PURPOSE:
* Add the PO number of an Approved Purchase
* Order to a package PL/SQL table, for processing by
* a statement-level database trigger.
* This proc is called by the row-level trigger to record
* approved POs which require further processing
* which cannot be done at the row-level, due to the 'mutating
* table' restriction.
*
* ARGUMENTS:
* Pono_in Purchase Order number
*
* LIMITATIONS:
* Only for use by database triggers.
**********************************************************
*/
procedure
Register_Approved_PO
(Pono_in IN VARCHAR2)
is
begin
--
PMPOT_index := PMPOT_index + 1;
PMPOT(PMPOT_index).Pono_f := Pono_in ;
--
end Register_Approved_PO;
/*
**********************************************************
* PROCEDURE:
* Fixup_Registered_POs
*
* PURPOSE:
* Call procedure 'Fixup_PO' for
* all PO's listed in table PMPOT.
* This provides additional processing following approval
* of a Preventive Maintenance Purchase Order.
*
* ARGUMENTS:
* nil
* LIMITATIONS:
* Intended for use only by database triggers.
**********************************************************
*/
procedure
Fixup_Registered_Approved_POs
is
i binary_integer := 0;
tmp_pono po_header.po_no%type := null;
begin
--
for i in 1..PMPOT_index loop
tmp_pono := PMPOT(i).Pono_f;
Fixup_Approved_PO(tmp_pono);
end loop;
--
PMPOT.DELETE;
PMPOT_index := 0;
--
end Fixup_Registered_Approved_POs;
/*
**********************************************************
* PROCEDURE:
* Fixup_Approved_PO
*
* PURPOSE:
* Do whatever is needed for a single purchase order. This
* code is not affected by the 'mutating table' problem.
*
* ARGUMENTS:
* pono_in PO Number
*
* LIMITATIONS:
* Only for use by database triggers.
**********************************************************
*/
procedure
Fixup_Approved_PO
(pono_in IN VARCHAR2)
is
begin
--
-- Do whatever is needed...
--
end Fixup_Approved_PO;
--...
--...
end PO_PKG;
/
create or replace trigger po_post_update_row
after update of po_status on po_header
for each row
begin
/*
** If the PO type is 'Preventive Maintenance' and is approved,
** register it for extra processing at statement level.
*/
if (:new.po_type = PO_PKG.PO_TYPE_PREVENTIVE_MAINTENANCE) then
if :new.po_status = PO_PKG.PO_STATUS_APPROVED then
PO_PKG.Register_Approved_PO(:new.po_no);
end if;
end if;
end;
/
create or replace trigger po_post_update_stmt
Received on Wed Sep 27 2000 - 14:23:30 CDT
