My After Insert Trigger is not firing

[kaliappan durairaj]

Hi i have written an After Insert Trigger as below and it is not updating the value as expected for the current record/row when i try to insert a row from the application.
can anyone help me on this.

TBL has two columns COLUMN1 and COLUMN2

CREATE OR REPLACE TRIGGER TRIGGER_AFTER_TBL
AFTER INSERT ON TBL

DECLARE

BEGIN

UPDATE TBL SET COLUMN2 = 'A' WHERE COLUMN2 IS NULL;

END;

[Ireneusz Kordal]

You must use 'FOR EACH ROW' clause - without this a trigger is a 'statement level' trigger, not 'record level' trigger,
and it fires only once on each statement, not on each row - for example INSERT INTO talbe SELECT * FROM table2 could insert hundred or thousands rows,
but the trigger is fired only once, and it is not related to any particular row - you cannot check inserted values etc.

Instruction: UPDATE TBL SET COLUMN2 = 'A' WHERE COLUMN2 IS NULL; updates records in a whole table, not only in changed record.
If you want to check only values of this particular new 'inserted' row, you must use a BEFORE trigger, not AFTER, and inside the tigger use a ':NEW pseudorecord
to check and change inserted values.

Try this:

CREATE OR REPLACE TRIGGER TRIGGER_BEFORE_TBL
BEFORE INSERT ON TBL
ON EACH ROW

BEGIN
  IF :NEW.column2 IS NULL THEN
        :NEW.COLUMN2 := 'A';
  END IF;
END; 

[kaliappan durairaj]

Hi Kordal thank you for responding my queries.

we cant use 'FOR EACH ROW' clause in my example, because i am updating the same table using the After insert Trigger.
I have tried 'FOR EACH ROW' in my After insert Trigger and when i try to insert a record in the table through the application
I got mutating exception and my insert didnt happen.

next is, i have tried the example provided by you already, the problem i faced is when i do a multiple insert from the application
using CMP bean then the column value is not updated properly except the first record.

The case here is:

I try to insert 10 rows through the application,

When i use Before insert Trigger as like your example, then only one record that is the first record got updated properly. here only one row updated properly.

when i use After insert trigger (my example) , then only the latest or the current record was not updated properly. here 9 rows were update properly.

Any suggestions here

[Ireneusz Kordal]

It seems to me that your application (CMP bean) handles inserting in this way, that it inserts records first,
then later performs an update, thus changes made in the before insert trigger
are overwritten by the update.

Please clarify what is the reason to perform such kind of update inside the trigger ?
This SQL: UPDATE TBL SET COLUMN2 = 'A' WHERE COLUMN2 IS NULL;
performs a full table scan to identify rows with nulls in COLUMN2, it may work fast if the table contains 10-100 rows,
but consider what can happen if the table contains thousands or millions rows - firing this query on each insert can kill the application.

[Tom Reilly]

In other words, why not just define COLUMN2 as not null with a default value of 'A'?