Postgresql Trigger example

Question

I created a trigger when a row updated trigger will change that row. When I update a column I get an error. How can I solve?

ERROR: stack depth limit exceeded HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

CREATE OR REPLACE FUNCTION ynt_call()
RETURNS TRIGGER AS $$
BEGIN
     IF TG_OP = 'UPDATE' THEN
       UPDATE ynt.a_test SET date_time = now();
    END IF;
  RETURN NEW;
 END;
 $$ language 'plpgsql';
 CREATE TRIGGER update_call AFTER UPDATE ON ynt.a_test FOR EACH ROW EXECUTE PROCEDURE ynt_call();

It looks you are in an infinite loop. To change the values in your trigger use the new and old keywords. — ChrisB
– ChrisB, Commented Nov 3, 2016 at 10:23
You should have looked closer to the example in the manual: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Nov 3, 2016 at 11:00

Houari · Accepted Answer · 2016-11-03 10:22:16Z

1

Of course you're looping until the end of time, because you are updating row when updating row. You should set the field date_time using the special variable new as following:

CREATE OR REPLACE FUNCTION ynt_call()
RETURNS TRIGGER AS $$
BEGIN
     IF TG_OP = 'UPDATE' THEN
       new.date_time = now();
    END IF;
  RETURN NEW;
 END;
 $$ language 'plpgsql';
 CREATE TRIGGER update_call AFTER UPDATE ON ynt.a_test FOR EACH ROW EXECUTE     PROCEDURE ynt_call();

answered Nov 3, 2016 at 10:22

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Trigger example

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related