How to fire trigger after every insert - Oracle (Bulk insert)

Question

I want the trigger to run after each and every record that gets inserted.

This validation works fine if I have a simple insert like this:

insert into g_dossier values
               (112334, 'BBT', 'LPO','TTS','Y') ;

However, when it’s like a bulk insert like this:

INSERT INTO g_piece(
                refpiece,
                typpiece,
                class_piece
                group_piece
                flag_piere)    
SELECT         :new.element_num,
                PROC_TYPE,
                DECODE( piece_it, 'F', 'FTTR', 'N', 'FTTR', NULL ),
                DECODE( piece_it, 'T', 'TTSN', 'N', 'TTSN', NULL ),
                'N'
FROM    t_elements
WHERE   :new.db_piece_flag = 'Y';

The trigger mutates. I want that the validation also work when done as a bulk insert.

The query causing this problem is

SELECT COUNT(*)
INTO existing_cmcl_cnt
FROM g_piece cmcl
WHERE cmcl.class_piece= :new.class_piece

The problem is that this query is called in a trigger applied on the same table "g_piece". When I proceed with a simple insert (insert into g_piece values(...)), I do not have this problem.

How can I avoid this problem? Thanks.

Answer 1

Here you should change the query and insert the result of a SELECT ... FROM some join, not using a trigger at all. You want that when inserting into table X, a trigger also inserts into the same table, which is not possible (it would recurse).

If you cannot change the query, you should rename your table, create a view to the table with the old name, and create a TRIGGER INSTEAD OF INSERT ON that view FOR EACH ROW that would INSERT INTO into the real table the result of a SELECT ... FROM some join.

Answer 2

If you're using Oracle 11G, you can take a look to Compound triggers to avoid the mutating-table error. Take a look: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD