wood burning stoves 2.0*
The moose likes JDBC and the fly likes My After Insert Trigger is not firing Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "My After Insert Trigger is not firing" Watch "My After Insert Trigger is not firing" New topic
Author

My After Insert Trigger is not firing

kaliappan durairaj
Greenhorn

Joined: Nov 24, 2008
Posts: 9
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
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
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:
kaliappan durairaj
Greenhorn

Joined: Nov 24, 2008
Posts: 9
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
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
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
Rancher

Joined: Jun 01, 2010
Posts: 618
In other words, why not just define COLUMN2 as not null with a default value of 'A'?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: My After Insert Trigger is not firing