Thread: TRIGGER Question

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