Hello fellow stack overflow wizards. I need to create a Trigger and a Trigger function in a PostgreSQL DB that updates a table from the DB "B" which is connected to DB "A" through Foreign Data Wrapper (FDW). My goal is to have a table in Database B that is automatically updated when a table from database A is changed (Since i'm using FDW, i'm joining the tables in database B, as they have the same data as in DB A). Here's a schema:
Database A
| Table 1 | Table 2 | Table 3 | Table 4 |
|---|
Database B
| Table 1 | Table 2 | Table 3 | Table 4 | Table Join 1-2 |
|---|
I've tried this code for the Trigger Function
create or replace function update.table()
returns trigger AS $$
BEGIN
if new.table1.column<>old.table1.column
then
INSERT INTO Table join 1-2 (column 1 <from table 1>,
column 2 <from table 2>,
column 3 <from table 1>,
column 4 <from table 2>
)
VALUES (NEW.Column 1,
NEW.Column 2,
NEW.column 3,
NEW.column 4
);
RETURN NULL;
END if;
END;
$$ LANGUAGE PLPGSQL;
And this code for the Trigger
CREATE trigger tg_update
AFTER insert or update
on table A
FOR EACH ROW
EXECUTE PROCEDURE update.table();
It's not working, the data in the Table join 1-2 is not being updated, altough the trigger function and the trigger itself didn't show any errors
Thanks in advance for any ideas or help! I'm also struggling to find content and examples for triggers in PostgreSQL, if you know a good place to find this kind of content, i would appreciate to know!
