Subject RE: [firebird-support] Trigger Fires for Each Column Changed Instead of Once per Row Update
Author Leyne, Sean

Paul,

 

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?

Documents Table:  dm_column_1, dm_column_2, dm_column_3
Documents_Log Table:  dl_column_1, dl_column_2, dl_column_3

SET TERM ^ ;
CREATE TRIGGER documents_after_u FOR documents ACTIVE
AFTER UPDATE POSITION 3
AS
 BEGIN
   INSERT INTO documents_log
     (dl_column_1, dl_column_2, dl_column_3)
   VALUES
     (new.dm_column_1, new.dm_column_2, new.dm_column_3);
 END^
SET TERM ; ^

 

<SL> This logic will create a single record for an update, which is your goal.

 

<SL> If you are seeing several log entries, the problem is with how the columns are being updated – it is likely that each column is being updated by a SQL statement, when the column value changes in the client.  This would result in the trigger firing for each separate update.