• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

My After Insert Trigger is not firing

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 618
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In other words, why not just define COLUMN2 as not null with a default value of 'A'?
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic