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.

Belayer · Accepted Answer · 2022-01-05 20:06:27Z

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;

answered Jan 5, 2022 at 20:06

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

3 Comments

Algorant Over a year ago

Thank you for the explanation. For our use case, the insistence is on doing this with trigger functions. I did bring up views in the initial discussion but I do not remember why the path forward ended up being triggers. I will inquire further and follow up, maybe you are right. =)

Belayer Over a year ago

Then the solution proposed by @Edouard is about as good as it gets. You will probably both A and B from it. Also an often overlooked issue with trigger based implementation is TRUNCATE. What do you do then?

Algorant Over a year ago

Also a good question! I will discuss it with my counterpart. Thank you very much.

Edouard · Accepted Answer · 2022-01-06 06:33:16Z

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.

edited Jan 6, 2022 at 6:33

answered Jan 5, 2022 at 19:43

Edouard

7,1291 gold badge11 silver badges24 bronze badges

2 Comments

Algorant Over a year ago

This is wonderful, thank you. I will look into (B) once I have a working A. I had a little bit of trouble understanding the WHERE clause of UPDATE and DELETE functions. Let's say the primary key column in both tables is called pk, and I am changing col1 col2 col3 in both tables. What would the syntax be for the WHERE clause?

Edouard Over a year ago

Let's say the primary key is defined on (col2, col3, col7) then the WHERE clause is WHERE col2 = NEW.col2 AND col3 = NEW.col3 AND col7 = NEW.col7

Collectives™ on Stack Overflow

Postgres Trigger to INSERT UPDATE DELETE on similar derivative table

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related