Creating trigger for every new insertion in postgresql

Question

I have two table table1 and table2 in two different database. Now I have created a trigger which will insert in table2 whenever a new row is inserted in table1. Below is my trigger

CREATE TRIGGER sync_user_table AFTER INSERT ON table1
for each row execute procedure sync_and_maintain_users_table()

CREATE OR REPLACE function sync_and_maintain_users_table()
returns trigger as
$BODY$
begin
    insert into global_db.public.table2
    values (user_uuid, user_registration_date, user_service_provider);
end;
$BODY$
language plpgsql;

But the above trigger is not working. Neither I am getting any errors, I am not sure what's wrong.

pifor · Accepted Answer · 2020-07-04 10:10:24Z

2

You may have no error when creating the trigger but you likely have errors at run-time:

CREATE OR REPLACE function sync_and_maintain_users_table()
returns trigger as
$BODY$
begin
    insert into global_db.public.table2
    values (user_uuid, user_registration_date, user_service_provider);
end;
$BODY$
language plpgsql;
CREATE FUNCTION

CREATE TRIGGER sync_user_table AFTER INSERT ON table1
for each row execute procedure sync_and_maintain_users_table();
CREATE TRIGGER

insert into table1 values(1);
ERROR:  cross-database references are not implemented: "global_db.public.table2"
LINE 1: insert into global_db.public.table2
                    ^
QUERY:  insert into global_db.public.table2
    values (user_uuid, user_registration_date, user_service_provider)
CONTEXT:  PL/pgSQL function sync_and_maintain_users_table() line 3 at SQL statement

In PostgreSQL you cannot reference directly another database with database.schema.object syntax.

And if you fix this you have another error:

CREATE OR REPLACE function sync_and_maintain_users_table()
returns trigger as
$BODY$
begin
    insert into public.table2
    values (user_uuid, user_registration_date, user_service_provider);
end;
$BODY$
language plpgsql;
CREATE FUNCTION

insert into table1 values(1);
ERROR:  column "user_uuid" does not exist
LINE 2:     values (user_uuid, user_registration_date, user_service_...
                    ^
HINT:  There is a column named "user_uuid" in table "table2", but it cannot be referenced from this part of the query.
QUERY:  insert into public.table2
    values (user_uuid, user_registration_date, user_service_provider)
CONTEXT:  PL/pgSQL function sync_and_maintain_users_table() line 3 at SQL statement

You need to add code to initialize variables used in VALUES clause. See examples in https://www.postgresql.org/docs/12/plpgsql-trigger.html.

answered Jul 4, 2020 at 10:10

pifor

7,9722 gold badges11 silver badges18 bronze badges

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

6 Comments

Lokesh Pandey Over a year ago

But one of my table is in another database. So how can I write this function to insert the values after successful insertion on table1 ?

Lokesh Pandey Over a year ago

I have changed the query for insertion like this insert into global_db.public.table2 values (NEW.user_uuid, NEW.user_registration_date, NEW.user_service_provider);

pifor Over a year ago

You can try to do this with postgres_fdw or dblink extensions but note sure it's a good idea because you would be using distributed transactions. It would be easier to use different schemas in the same database (databases in same instance in PostgreSQL don't behave like databases in same instance like in SQL Server).

Lokesh Pandey Over a year ago

After making the above changes and I added my table2 in the same db and in same schema, it still doesn't work

pifor Over a year ago

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

|

Collectives™ on Stack Overflow

Creating trigger for every new insertion in postgresql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related