CODEWITHSUNDEEP
postgresqltriggers
sm4ll_3gg
sm4ll_3gg

Reputation: 247

Update row in trigger without recursion

I have two tables: 

CREATE TABLE first (
    id text primary key,
    updated_at timestamp,
    data text
);

CREATE TABLE second (
    id text REFERENCES first (id),
    book_error text,
);

and I need to update updated_at field in first table always, when any of these tables has updated. I wrote this: 

CREATE FUNCTION update_timestamp() RETURNS trigger AS $$
    BEGIN
        UPDATE first 
            SET updated_at = current_timestamp
            WHERE id = NEW.id;

        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        EXECUTE format('CREATE TRIGGER update_timestamp
                        BEFORE INSERT OR UPDATE ON %I
                        FOR EACH ROW EXECUTE PROCEDURE update_timestamp()',
                        t);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

But it's not working because update statement inside my trigger causes call of this trigger again before executing.
How can I do update inside trigger without firing it trigger again?

Upvotes: 0

Views: 306

Answers (2)

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

From Postgresql documentation,

pg_trigger_depth() - current nesting level of PostgreSQL triggers (0 if not called, directly or indirectly, from inside a trigger)

You can use this inside your trigger function to check if it is called from inside trigger

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema = 'public'
    LOOP
        EXECUTE format('CREATE TRIGGER update_timestamp
                        BEFORE INSERT OR UPDATE ON %I
                        FOR EACH ROW
                        WHEN (pg_trigger_depth() = 0)
                        EXECUTE PROCEDURE update_timestamp()',
                            t);
        END LOOP;
    END;
    $$ LANGUAGE plpgsql;


test=# Insert into first select 1,now(), 'test';
INSERT 0 1

test=# select * from first;
 id |         updated_at         | data 
----+----------------------------+------
 1  | 2018-10-29 20:18:25.227281 | test
(1 row)

test=# Insert into second select 1, 'test_error';
INSERT 0 1
test=# select * from first;
 id |         updated_at         | data 
----+----------------------------+------
 1  | 2018-10-29 20:19:07.456737 | test

Upvotes: 1

klin
klin

Reputation: 121919

Per the documentation:

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

Use the variable in the trigger function:

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
    IF TG_TABLE_NAME = 'first' THEN
        NEW.updated_at = current_timestamp;
    ELSE
        UPDATE first 
            SET updated_at = current_timestamp
            WHERE id = NEW.id;          
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions