Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delete from table contained in After Insert Trigger - Oracle

Posted on 2013-12-17
6
Medium Priority
?
1,726 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.
Suggested Courses

971 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