| Subject | Trigger Fires for Each Column Changed Instead of Once per Row Update |
|---|---|
| Author | |
| Post date | 2014-01-24T19:37:24Z |
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.
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.
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 ; ^
Thank you,
---------------------------------------------------------------------------------
Confidentiality Notice: This message is the property of the United States Bankruptcy Court for the Western District of Kentucky. It may be legally privileged and/or confidential and is intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or taking any action in reliance on the information contained herein is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this message.
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.
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.
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 ; ^
Thank you,
---------------------------------------------------------------------------------
Confidentiality Notice: This message is the property of the United States Bankruptcy Court for the Western District of Kentucky. It may be legally privileged and/or confidential and is intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or taking any action in reliance on the information contained herein is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this message.