Postgres Trigger to INSERT UPDATE DELETE on similar derivative table

Question

Description:

• I am running postgresql 13
• I have two tables under different schemas, t1 and t2.
• t2 is derivative of t1 in the sense that they share all the same columns and data, but t2 is always downstream of t1 as far as validity.
• The rows in both tables share the same primary key, which is what I assume would be used as the link between them.

The ask:

• I would like to create a trigger that reflects any changes in t1 and syncs t2 to be the same.
• I started with INSERT or UPDATE, but if DELETE is easily added, I would like to implement that as well.

Trigger Code:

-- Trigger for t1 to t2 --

CREATE OR REPLACE FUNCTION t1_schema.sync_trigger()

RETURNS TRIGGER AS

$$
BEGIN
    INSERT INTO t2_schema.t2 (col1, col2, col3)
    VALUES (NEW.col1, NEW.col2, NEW.col3);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
    
    
CREATE TRIGGER t1t2_test_sync
AFTER INSERT OR UPDATE ON t1_schema.t1
FOR EACH ROW
EXECUTE PROCEDURE t1_schema.sync_trigger()

When I execute this code and do a test UPDATE on t1, the same row on t2 does not reflect the changes or give me any errors.

I have tried:

• Discretely labeling all rows as updated with NEW. format, but run into the problem of primary key column not being editable in t2.
• Adding a WHERE clause after the VALUES clause, something like WHERE primary_key=NEW.primary_key, but I get an error.
• Another option I have seen is adding an IF statement before the INSERT, or adding a WHEN clause in the trigger, but neither have worked.

Answer 1

(A) Solution based on triggers

You maybe get an error when updating a row in t1 because your trigger function tries to insert a new row in t2 which has alreday been inserted in t2 by the same trigger function when it has been inserted in t1. You need to duplicate and specialize your trigger functions, one for insert, one for update, one for delete because the treatment to be triggered on t2 is different :

CREATE OR REPLACE FUNCTION t1_schema.sync_trigger_insert()
RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO t2_schema.t2 (col1, col2, col3)
    VALUES (NEW.col1, NEW.col2, NEW.col3);
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
    
CREATE TRIGGER t1t2_test_sync_insert
AFTER INSERT ON t1_schema.t1
FOR EACH ROW EXECUTE PROCEDURE t1_schema.sync_trigger_insert() ;

CREATE OR REPLACE FUNCTION t1_schema.sync_trigger_update()
RETURNS TRIGGER AS
$$
BEGIN
    UPDATE t2
       SET col1 = NEW.col1
         , col2 = NEW.col2
         , col3 = NEW.col3
     WHERE primary_key_t2 = NEW. primary_key_t1 ; -- primary_key_t2 must be replaced by the set of columns which are in the primary key of t2 with AND operators, the same for NEW.primary_key_t1
    RETURN NEW;
END
$$ LANGUAGE plpgsql;
    
CREATE TRIGGER t1t2_test_sync_update
AFTER UPDATE ON t1_schema.t1
FOR EACH ROW EXECUTE PROCEDURE t1_schema.sync_trigger_update() ;

CREATE OR REPLACE FUNCTION t1_schema.sync_trigger_delete()
RETURNS TRIGGER AS
$$
BEGIN
    DELETE FROM t2
     WHERE primary_key_t2 = NEW. primary_key_t1 ; -- primary_key_t2 must be replaced by the set of columns which are in the primary key of t2 with AND operators, the same for NEW.primary_key_t1
    RETURN OLD; -- NEW is not available for triggers ON DELETE
END
$$ LANGUAGE plpgsql;
    
CREATE TRIGGER t1t2_test_sync_delete
AFTER DELETE ON t1_schema.t1
FOR EACH ROW EXECUTE PROCEDURE t1_schema.sync_trigger_delete() ;

(B) Solution based on foreign key

It is possible that a foreign key on t2 (col1,col2,col3) referencing t1 (col1, col2, col3) with the options ON UPDATE CASCADE ON DELETE CASCADE may deliver your expected result in a much more simple and efficient way, see the manual. In this case, you don't need the triggers ON UPDATE and ON DELETE anymore, but you still need the trigger ON INSERT.

Answer 2

Your best approach is to not create t2 as a table. Instead create it as a VIEW on t1. This totally eliminates triggers to keep them synchronized because the actual source is the same. Follows the concept to store a single data point in only 1 place. Keep in mind that if you store a single piece in 2 places, 1 on them will be wrong at some point. (see demo).

create view soq2.t2 as 
            select * 
              from soq1.t1;

Also if you need column names to change then use an alias during the create view;

create view soq2.t2a as 
       select t1_id  as t2_id
            , name   as t2_name 
            , status as t2_status
         from soq1.t1;