0

In my postgresql database I have an "on update" trigger on a table (call it table A) that audits record changes. I also have another update trigger on a second table (table B) that changes a value in table A. Naturally, the firing of the trigger on table B causes the trigger on table A to fire as well, as table A is updated.

To prevent this (since I only want to audit USER changes, not automatic changes), my trigger on table A currently ignores changes to the fields that are modified by the table B trigger. However, while this works, this approach doesn't sit well with me - what if a user modifies one of the fields that are also modified by the table B trigger? That change won't be logged (unless they also modify other fields that aren't ignored).

So my question is this: is there a "better way" to prevent the table A audit trigger from running in response to changes due to the table B trigger?

ibrewster
  • 3,482
  • 5
  • 42
  • 54
  • see my answer here: http://stackoverflow.com/questions/14085258/how-do-i-get-a-column-with-consecutive-increasing-numbers-without-having-any-n for a solution with an alternating-bit-scenario. – wildplasser Jan 22 '14 at 18:29
  • @wildplasser So as I understand it, I would add this alternating-bit column to my table a, have my table b trigger set it, and have my table a trigger NOT do anything if it is set other than unset it. Is that correct? – ibrewster Jan 23 '14 at 18:06
  • I think you'll have to add it to _both_ tables. The logic is: the frontend (or user) should not touch it, the trigger function flips it when it changes the row. So if the trigger(function) finds out that OLD and NEW have different values, it will know that a trigger already has touched that row. – wildplasser Jan 23 '14 at 18:55

0 Answers0