TRIGGER Question

From: sbob <sbob(at)quadratum-braccas(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: TRIGGER Question
Date: 2021-05-28 04:31:38
Message-ID: 2644591d-c517-ccb5-1e1e-ccce57f1d99c@quadratum-braccas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

All;

I want to create a trigger function that can set a value for a column if
the column was not specified in the update statement.

I have this so far;

CREATE OR REPLACE FUNCTION set_last_updated_by() returns trigger AS
$$
BEGIN
                RAISE NOTICE '[%] [%]', NEW.last_updated_by,
OLD.last_updated_by;

    IF (TG_OP = 'INSERT') THEN
                IF NEW.last_updated_by IS NULL THEN
                        NEW.last_updated_by='BACK_OFFICE';
                        RAISE NOTICE 'SETTING NEW.last_updated_by to
BACK_OFFICE for INSERT';
                END IF;

        ELSIF (TG_OP = 'UPDATE') THEN

                IF NEW.last_updated_by IS NULL THEN
                        NEW.last_updated_by='BACK_OFFICE';
                        RAISE NOTICE 'SETTING NEW.last_updated_by to
BACK_OFFICE for update';
                END IF;
        END IF;

        RETURN NEW;

END;
$$ LANGUAGE plpgsql;

DROP TRIGGER user_last_update_by_trg ON users;

CREATE TRIGGER user_last_update_by_trg
BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_last_updated_by();

However if the row to be updated already has a value for last_updated_by
even if the last_updated_by column is not specified in the update
statement, then the "IF NEW.last_updated_by IS NULL THEN" is never fired..

Thoughts?

Thanks in advance

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2021-05-28 05:40:19 Re: TRIGGER Question
Previous Message Laurenz Albe 2021-05-28 02:45:17 Re: Migrating local PG instance to AWS RDS?