7

I have 2 triggers on one table; one works for INSERTs :

CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "add_info"();

This updates some values in the table.
And one for UPDATEs (to fill a history table):

CREATE TRIGGER "set_history"
BEFORE UPDATE ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "gener_history"();

The problem is that when I insert a new row in the table the procedure "add_info"() makes an update and therefore fires the second trigger, which ends with an error:

ERROR:  record "new" has no field "field1"

How can I avoid this?

  • 2
    It sounds like an error in the trigger logic. Could you show the trigger procedure that throws it? – dezso Jun 6 '15 at 18:31
  • 1
    yes it does - what do you get if you just update a row? I'd expect the same error. – Jack Douglas Jun 6 '15 at 18:46
  • You both are right: it was an error in my second trigger. My problem is solved but nevertheless I was thinking of something like the pg_trigger_depth() proposed by @Akash, which I need to test further now, because for the moment my trigger doesn't do anything at all when I use this test. Thanks to all of you ! – dd_a Jun 7 '15 at 21:36
12

(Obvious error in the trigger logic aside.)
In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops).
This typically performs better and is simpler and cleaner:

CREATE TRIGGER set_history
BEFORE UPDATE ON field_data
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE gener_history();

The expression pg_trigger_depth() < 1 is evaluated before the trigger function is entered. So it evaluates to 0 in the first call. When called from another trigger, the value is higher and the trigger function is not executed.

  • Thank you. Regarding the solution proposed by @Akash, as I said in a comment above, the test on pg_trigger_depth() = 0 didn't give me any result, but I found that preceding my code inside the body of the trigger function by "IF pg_trigger_depth() <> 1 THEN RETURN NEW; END IF;" as explained here : depesz.com/2012/02/01/waiting-for-9-2-trigger-depth does work. I'm mentioning this for future users, but your solution looks even simpler, and I will test it asap. – dd_a Jun 10 '15 at 8:16
  • @dd_a: pg_trigger_depth() = 0 in Akash's answer makes no sense inside a trigger function. I added a comment there and more explanation to my answer. My solution should be simpler and faster for your use case than what Depesz presented in his blog. – Erwin Brandstetter Jun 11 '15 at 4:22
  • @G_Hosa_Phat: Please ask new questions as new questions. Comments are not the place. You can always link to this one for context. – Erwin Brandstetter Sep 9 '15 at 11:18
  • @ErwinBrandstetter: I apologize. I didn't think my questions worthy of a whole new thread as they were in the context of the current discussion. I will remove my comment and migrate the questions to a new thread. – G_Hosa_Phat Sep 9 '15 at 13:31
4

If you dont want the update trigger to be executed when the its called from within the insert trigger, you can surround your statements with a condition of pg_trigger_depth() which returns the depth, which wont be 0 when you are running the trigger directly/indirectly from another trigger.

So, within your function gener_history(), you can do something like this

IF pg_trigger_depth() = 1 THEN
.. your statements..
END IF;

Here's another example: http://www.depesz.com/2012/02/01/waiting-for-9-2-trigger-depth/

  • It has to be noted that pg_trigger_depth() = 0 inside a trigger is always false because the minimum trigger depth is 1 there. You only get 0 outside of a trigger. – Erwin Brandstetter Jun 10 '15 at 18:20
  • right, seems I made a mistake, I've correct it, thanks – Akash Jun 11 '15 at 12:08
1

SUGGESTION #1

Remove the AFTER INSERT trigger and call add_info from your app

SUGGESTION #2

Change the AFTER INSERT trigger into BEFORE INSERT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.