At 08:37 a.m. 25/01/2014, paul_malone@... wrote:

>I want to track all changes to a table (documents) by inserting just one row into another table (documents_log) each time a row is updated in the documents table.
>
>The problem I am having with the trigger below is that it seems to fire for every column that is changed, instead of just one time for the row update.

The trigger will fire every time the ROW is updated.

>For example, if three columns are changed when a row in the documents table is updated, then three rows are inserted in the documents_log table.
>If two columns are changed when a row in the documents table is updated, then two rows are inserted in the documents_log table.
>If only one column is changed when a row in the documents table is updated, then one row is inserted in the documents_log table.

Your application code is firing an update each time the user changes a field. To get the behaviour you want, change your application code so that it does not post changes until the user has completed the task.

>What do I need to do to the trigger so that only one row is inserted into the documents_log table when two or more columns are changed when a single row is updated in the documents table?

You don't need to do anything to the trigger. Firebird updates the WHOLE row at each Post, creating a new record version on disk each time. (This will become the new record version for the whole database once this user's transaction is COMMITTED. Until then, the changes are not visible to other transactions...and if the transaction is rolled back, it will never be seen.)

So review your application code and modify it so that nothing goes to disk until the user signals s/he is finished with that record..


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________