Question about triggers
Unholy wanted a trigger to fire once after 500 JDBC row inserts in a transaction, not per row. Doug Lawry suggested an AFTER trigger action instead of FOR EACH ROW. Jonathan Leffler noted REFERENCING cannot be used there, that it fires once per statement, and suggested inserting into a triggerless temp table then one INSERT...SELECT. C on-commit triggers were possible but hard.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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/