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