13

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?

András Váczi
  • 31,278
  • 13
  • 101
  • 147
dd_a
  • 191
  • 1
  • 3
  • 9
  • 2
    It sounds like an error in the trigger logic. Could you show the trigger procedure that throws it? – András Váczi Jun 06 '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 06 '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 07 '15 at 21:36

3 Answers3

25

(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 FUNCTION gener_history();

In Postgres 10 or older use the keyword PROCEDURE instead of FUNCTION. See:

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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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 : http://www.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 08: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 04: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 09 '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 09 '15 at 13:31
5

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/

Akash
  • 316
  • 2
  • 9
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

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520