Hey Bjarne,

DW fires separate SQL statement for each row in the DataWindow. FOR EACH ROW and FOR EACH STATEMENT will have same performance characteristic in this case.

However, say you have a statement like

UPDATE employee SET Salary = Salary * 1.03

and that the table contains 1000 rows

FOR EACH STATEMENT will fire once having 1000 rows in its "OLD" pseudo-table and 1000 rows in its "NEW" pseudo-table. In a "statement" level trigger as I remember you ar NOT guaranteed that row number X in OLD is the before-data matching row number X in NEW. So additional code may be required to compare OLD vs. NEW effectively.

FOR EACH ROW will fire 1000 times having each time just one row in its "OLD" pseudo-table and one row in its "NEW" pseudo-table. In a "row" level trigger you now that OLD and NEW correspond to same row.

That has potentially different performance characteristics.

HTH /Michael

I should mention that this is not a traditional log of errors, but rather a notification to another system to process the data further to the cloud.

We don't want to add any more columns to the table since it is a production table with lots of queries as is.

That's why we chose a trigger.

And that's why I would like the records to be all written before the trigger fires, and I hope that "FOR EACH STATEMENT" does the trick when usiing datastore.update() and a sqlca.of_commit() after that?

Bjarne

FWIW:

As a rule, I like to have detailed logging for errors, but not when everything works correctly.

One possibility:
For log entries you WRITE into a table, I would keep the BY ROW setting enabled.
Check the status when the event ends (all rows finished).
If NO ERROR, then rollback the row-by-row entries in the log table, and insert an overview entry*


*Something like "Processed rows x - y, all rows processed correctly."

Olan