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
