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
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? |