| 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: | Whole Thread | Raw Message | 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? |