Question about triggers

Hi all, actually I am developing an application. This app works against an Informix database, and in a proccess it inserts for exmaple, 500 records in a table. I need to make an update in the table after the records has been inserted, and i've been thinking about to make it with a trigger "for insert". The app is made in java, and I insert the records with JDBC inside a transaction. The problem is that I think that the trigger will be fired once for each row inserted in the table, but I just need that the trigger will be fired once when the transaction is commited. I know that is the transaction is rolledBack, the operations made by the triggers will be rolled back too, but the problem is that what the trigger have to do is a heavy proccess, and I want to fire it just once. Any help will be appreciated. Thanls

Insert triggers normally look like this: CREATE TRIGGER trigger_name INSERT ON table_name REFERENCING NEW AS alias_name FOR EACH ROW ( action_statement ); If you insert multiple rows as a single statement, but want the trigger to run only once, use AFTER instead of FOR EACH ROW: CREATE TRIGGER trigger_name INSERT ON table_name REFERENCING NEW AS alias_name AFTER ( action_statement ); -- Regards, Doug Lawry www.douglawry.webhop.org "Unholy" <dcalleja@gmail.com> wrote in message news:1120118490.713358.279220@g47g2000cwa.googlegroups.com... > Hi all, actually I am developing an application. This app works against > an Informix database, and in a proccess it inserts for exmaple, 500 > records in a table. > > I need to make an update in the table after the records has been > inserted, and i've been thinking about to make it with a trigger "for > insert". > > The app is made in java, and I insert the records with JDBC inside a > transaction. The problem is that I think that the trigger will be > fired once for each row inserted in the table, but I just need that the > trigger will be fired once when the transaction is commited. > > I know that is the transaction is rolledBack, the operations made by > the triggers will be rolled back too, but the problem is that what the > trigger have to do is a heavy proccess, and I want to fire it just > once. > > Any help will be appreciated. > > Thanls

But in this way the trigger will be fired once after each row is inserted, isn`t it???.

> Doug Lawry wrote: >> If you insert multiple rows as a single statement, but want the trigger to run >> only once, use AFTER instead of FOR EACH ROW: >> >> CREATE TRIGGER trigger_name >> INSERT ON table_name >> REFERENCING NEW AS alias_name >> AFTER ( >> action_statement >> ); You can't use the REFERENCING clause in a BEFORE or AFTER trigger. Unholy wrote: > But in this way the trigger will be fired once after each row is > inserted, isn`t it???. No - after the complete statement. Now, if you are repeatedly executing single inserts, yes - you're right. If you're doing INSERT INTO SomeWhere SELECT * FROM AnotherPlace, then it fires once. So, maybe you can exploit this - insert repeatedly into a triggerless temp table, then when you're ready to finish up, run INSERT INTO FinalTable SELECT * FROM TempTable. There are also ways of registering an on-commit trigger if you code in C in a suitable way. Definitely not for the faint of heart! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/