Solved

Delete from table contained in After Insert Trigger - Oracle

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

910 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

24 Experts available now in Live!

Get 1:1 Help Now