Solved

Delete from table contained in After Insert Trigger - Oracle

Posted on 2013-12-17
6
1,094 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

813 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