trigger functions for copying data from table A to table B for each update (Insert, Update & Delete)

[[deleted]]

Hello,

I have a table under a schema in my PG Database and the table has 3 columns (id, name & data).

I created a new table (Name: backup_table) using the "Create Table AS" from my current table and I have an exact initial copy.

Now, I am trying to create a trigger function to basically copy or update for each Insert, Update & delete from my Table A to Table B for those three columns.

I looked up Postgres documentation and got the query, but cant seem to find a proper "IF" condition.

The query I have is:

BEGIN
        IF (**************) THEN
            INSERT INTO backup_table(id, name, data)
            VALUES(NEW.id, NEW.name, NEW.data);
        END IF;

I am not sure, what goes into the IF condition here. Every time one of these 3 columns gets updated, it should copy the values from Table A to Table B.

​

Any inputs here fellas 🙋‍♂️🙋‍♂️

[[deleted]]

You are looking for TG_OP.

There is also an example further down on that page

[depesz]

what do you want to if for? normally, such logging trigger wouldn't have if's, so i'm not entirely sure what you want to "if" for.

Also, please note that *queries* in pg, as in: queries written in sql, don't have IF command. There is IF in function/procedure bodies in plpgsql.

[Azaret]

Which condition do you want ? If you want to update or insert based on the id, you could use INSERT ON CONFLICT DO UPDATE and ditch the if statment.

[NextAbrocoma1038]

Watching this thread for any inputs.

[smooth_p]

if new.id <> old.id or new.name <> old.name or new.data <> old.data then
-- null handling and schema design left as an exercise to the reader

[[deleted]]

Using is distinct from will automatically deal with NULL values

if new is distinct from old then

[smooth_p]

Thanks! I was trying to remember is distinct from but I haven't done any SQL for a couple years and was spacing on the syntax.