Question 267567 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql Trigger to Set Columns to NULL

I want to use a trigger in Postgresql to "delete" data from specific columns (set to NULL), when a other column in the same relation is updated. This could look like this in the trigger function:

IF OLD.column_1 IS DISTINCT FROM NEW.column_1 THEN
UPDATE relation_1 SET column_2 = NULL;

There already is a working trigger to delete data from other relations (see below). I’m not sure, if I can adapt the trigger function to my needs or if I need to implement a totally new trigger for that.

Current trigger and trigger function:

-- Function
CREATE OR REPLACE FUNCTION delete_results_trigger_function()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
$$
BEGIN
    IF OLD.column_1 IS DISTINCT FROM NEW.column_1 THEN

        DELETE FROM relation_2
        WHERE relation_2_id IN (
            SELECT relation_2_id FROM peak
            WHERE relation_1 = OLD.relation_1_id
        );
    END IF;
    RETURN OLD;
END;
$$

-- Trigger
CREATE TRIGGER delete_results_on_event_trigger
    AFTER UPDATE
ON relation_1
FOR EACH ROW
    EXECUTE FUNCTION delete_results_trigger_function();

The software using this database and the trigger writes at some point to the relation_1 updating several columns in this relation (e.g. column_2) including column_1 (which triggers the trigger function). I want the changes from the software to be written to the relation (not set to NULL). But when a update is made manually to relation_1.column_1 (e.g. set to NULL), the corresponding columns in relation_1 should be set to NULL as well.

I hope it is clear, what I want to do here.
Thanks for your answers in advance!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here is my solution for my problem (more like a workaround):

    CREATE OR REPLACE FUNCTION ... ... $$
BEGIN
    IF OLD.column_1 IS DISTINCT FROM NEW.column_1 THEN
        DELETE FROM relation_2
        WHERE relation_2_id = OLD.column_1
        ;
    END IF;
    IF NEW.column_1 IS NULL THEN
        NEW.column_2 = NULL;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER ... 
BEFORE UPDATE OF column_1
ON relation_1
FOR EACH ROW
EXECUTE FUNCTION delete_results_trigger_function()
;

  2. 0

    If you want to change the value of some columns in the same table and table row that made the trigger fire, you don’t use UPDATE (that might well cause an infinite loop and it not very efficient even without that).

    Instead, you define a BEFORE trigger that modifies NEW and return that modified row, which will then used as the new table row:

    CREATE FUNCTION ... RETURNS trigger
  LANGUAGE plpgsql AS
$$
   ...
   IF OLD.column_1 IS DISTINCT FROM NEW.column_1
   THEN
      NEW.column_2 = NULL;
   END IF;
   ...
   RETURN NEW;
$$;

    Change your current trigger into a BEFORE UPDATE trigger and make sure it returns NEW, then you can add that code to your current trigger.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search