Solved

Delete from table contained in After Insert Trigger - Oracle

Posted on 2013-12-17
6
978 Views
Last Modified: 2013-12-17
Under certain conditions a record inserted by application needs to be deleted.  I created an After Insert trigger, but I get the following error

ORA-04091 ' table KBM_MESSAGES is mutating, trigger/function may not see it'

How should I format such a trigger that I can delete the row being inserted (firing the trigger)

Note:  I do not want to rollback the transaction, it should not interfere with any other processing performed by application.

This is the source code of trigger:

create or replace trigger kbm_messages_trigger
    after insert on kbm_messages
    for each row
    declare
        v_exists number := 0;
            
   begin

      select count(*)      into v_exists
            from kbm_messages
            where message_id = :new.message_id
            and sent_by = 'RECLASS';
       
      if v_exists > 0 then
            delete from kbm_messages
                  where message_id = :new.message_id;
    end if;
end;
0
Comment
Question by:gantone1
6 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
You do not delete, but replace it.
Otherwise your whole process is flawed.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
How does the insert occur? If it's from a select statement, you might consider a merge insert when not matched and avoid the insert in the first place.
0
 

Author Comment

by:gantone1
Comment Utility
The insert is within a canned application we don't have source code for.  I want the record to be inserted except when the SENT_BY is 'RECLASS'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
If the "message_id" column is the primary key, then any insert with same key will be automatically rejected.
else ...
if "message_id" is NOT primary key, do you permit duplicates? what is the pk?

Post the table definition and indicate the primary key.

Therefore if the "sent_by" you refer to is ":new.sent_by", then use a before insert trigger and ignore the row.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
Comment Utility
ORA-04091 ' table KBM_MESSAGES is mutating, trigger/function may not see it'

"How should I format such a trigger?"

You need to use a multi-step approach for this, since Oracle does not support a row-level ("for each row") trigger using SQL statements to adjust: the trigger table itself; nor any tables related by foreign key, either parent table(s) or child table(s).

The multi-step approach involves these three objects:
1. a row-level trigger (including: "for each row") to simply save the rowid or primary key value of the row(s) being changed.
2. a place to temporarily hold these rowids or primary key values
3. an after-statement trigger (with no "for each row" clause) that will retrieve each rowid or primary key, then do the actual SQL work you want done.

First, create the temporary place to hold the rowid or primary key.  You have multiple options for this: a PL\SQL package variable if you only expect to ever have one row being processed at a time; a global temporary table; or a PL/SQL array.  I usually use a global temporary table.

Second, change your "for each row" trugger to simply insert the rowid (or primary key) of the row being inserted into your global temporary table.

Third, create a statement-level "after insert" trigger (with no "for each row" clause) to select the rowid(s) or primary key(s) from the GTT and do whatever SQL activity you want to happen in the triggering table.

If you create the GTT (global temporary table) with the default "on commit delete rows" option, you don't have to worry about cleaning up this table after processing the rows - that will happen automatically.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility
Try this, perhaps it will work:
CREATE OR REPLACE TRIGGER kbm_messages_trigger
  BEFORE INSERT
  ON kbm_messages
  FOR EACH ROW
DECLARE
  skip_row   EXCEPTION;
BEGIN
  IF :new.sent_by = 'RECLASS'
  THEN
    RAISE skip_row;
  END IF;
EXCEPTION
  WHEN skip_row
  THEN
    RAISE_APPLICATION_ERROR(-20000,'RECLASS Row skipped: '||:new.message_id);
END;
/

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now