RE: muttating table error

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFAFD8.B5E1600A
Content-Type: text/plain;

        charset="iso-8859-1"

Hi 'RC',
Have a loook at the attached.
Regards,
James Campbell.

-----Original Message-----
From: RC [mailto:rc_at_neomenia.com]
Sent: Thursday, 27 April 2000 4:54
To: Multiple recipients of list ORACLE-L Subject: muttating table error

Anyone have any examples to handle a trigger that selects and updates from the same table?    

thanks  

• -

------_=_NextPart_000_01BFAFD8.B5E1600A
Content-Type: application/octet-stream;

        name="mutate.sql"
Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment;

        filename="mutate.sql"

/*

• Sample PL/SQL code illustrating use of triggers and package = procedures
• to circumvent a mutating table problem.
  **
  
• James Campbell, February 2000.
  **
  
• This code is incomplete. It is provided for illustrative purposes = only.
• = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
  **
  
• Background and underlying assumptions:
  **
  
• 1. Table 'po_header' contains Purchase Order (PO) header records. =20
• 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=20
• Purchase Order is approved, and someone has decided to implement = that=20
• using database triggers rather than by some means which might be = more=20
• sensible - such as via a stored procedure. The special = processing causes=20
• 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 =3D = 'APPROVED'; PO_TYPE_PREVENTIVE_MAINTENANCE constant po_header.po_type%type =3D = '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 :=3D 0;
--
/*


**********************************************************

*  PROCEDURE:=20
*  Register_Approved_PO
*
*  PURPOSE:
*  Add the PO number of an Approved Purchase
*  Order to a package PL/SQL table, for processing by=20
*  a statement-level database trigger. =20
*  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.
*   =20
*  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 :=3D PMPOT_index + 1;
  PMPOT(PMPOT_index).Pono_f :=3D Pono_in ;
  --
end Register_Approved_PO;
/*


**********************************************************

*  PROCEDURE:=20
*  Fixup_Registered_POs
*
*  PURPOSE:
*  Call procedure 'Fixup_PO' for
*  all PO's listed in table PMPOT. =20
*  This provides additional processing following approval=20
*  of a Preventive Maintenance Purchase Order.
*   =20
*  ARGUMENTS:
*  nil
*  LIMITATIONS:
*  Intended for use only by database triggers.


**********************************************************

*/
procedure
Fixup_Registered_Approved_POs
is
  i binary_integer :=3D 0;
  tmp_pono po_header.po_no%type :=3D null;
begin
  --
  for i in 1..PMPOT_index loop
    tmp_pono :=3D PMPOT(i).Pono_f;
    Fixup_Approved_PO(tmp_pono);
  end loop;
  --
  PMPOT.DELETE;
  PMPOT_index :=3D 0;
  --
end Fixup_Registered_Approved_POs;
/*


**********************************************************

*  PROCEDURE:=20
*  Fixup_Approved_PO
*
*  PURPOSE:
*  Do whatever is needed for a single purchase order. This
*  code is not affected by the 'mutating table' problem.
* =20
*  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,=20

** register it for extra processing at statement level.

*/
  if (:new.po_type =3D PO_PKG.PO_TYPE_PREVENTIVE_MAINTENANCE) then
    if :new.po_status =3D 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
after update on po_header