CODEWITHSUNDEEP
postgresqlplpgsqldatabase-trigger
Shesh Kumar Bhombore
Shesh Kumar Bhombore

Reputation: 69

How to create a trigger in such that entire old record (existing table) goes to another table before updates happen in the existing table

I'm new to Postgres and trigger functions. I'm trying to create a trigger in postgres where the table schema_name1.table_name_abc gets inserted or updated every minute. Now before it gets inserted/updated, I want to capture the entire old records (including the newly inserted rows) and move it to another table schema_name1.table_name_xyz

Is it possible to move entire records before update happens also the newly inserted row to another table using trigger?

Below is the query that I'm trying to run. I'm unable to get any logic for the "Insert into" inside if conditions

CREATE OR REPLACE FUNCTION schema_name1.table_name_abc()
  RETURNS trigger AS
$BODY$
BEGIN
BEFORE UPDATE ON schema_name1.table_name_abc
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO  schema_name1.table_name_xyz VALUES(<no idea what to provide here>);
    END IF;
END;

BEFORE INSERT ON schema_name1.table_name_abc
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO  schema_name1.table_name_xyz VALUES(<no idea what to provide here>);
    END IF;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION schema_name1.table_name_abc()
  OWNER TO sys_object_owner;

Upvotes: 1

Views: 368

Answers (1)

giorgiga
giorgiga

Reputation: 1778

The values you need are available as OLD (eg: the value of FIELD_NAME is in OLD.FIELD_NAME).

See https://www.postgresql.org/docs/10/static/plpgsql-trigger.html (specifically, look at Example 42.4)

Upvotes: 1

Related Questions