Solved

Delete from table contained in After Insert Trigger - Oracle

Posted on 2013-12-17
6
1,160 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
ID: 39724781
You do not delete, but replace it.
Otherwise your whole process is flawed.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39724872
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
ID: 39724887
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
ID: 39724930
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 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39725255
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
ID: 39725346
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

830 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