CODEWITHSUNDEEP
sqlpostgresqltriggers
sh4rkyy
sh4rkyy

Reputation: 402

Exclude/ignore certain column on update trigger

I want to exclude certain column from firing a trigger on updates, I tried few variations of the code shown here, but it doesn't work (not updating import_timestamp) when processed_on was not null before update.

I tried adding this commented out OR clause but still the same. Any ideas?

Expected behaviour is to update import_timestamp on update on any column except processed_on, and do not update it on processed_on updates (it's not possible that processed_on update will occur in same transaction with other columns).

DBFIDDLE

create table test
(
    id serial,
    some_value integer,
    import_timestamp timestamp,
    processed_on timestamp
);

insert into test(some_value, import_timestamp, processed_on) 
values (1312, '2021-02-02 20:00:00', '2021-02-02 20:44:00'),
       (124, '2021-02-02 20:10:00', null),
       (500, '2021-02-02 20:20:00', null);
 
 
CREATE OR REPLACE FUNCTION trg_update_import_timestamp (
)
RETURNS trigger AS
$body$
BEGIN
    IF OLD.processed_on = NEW.processed_on 
    --OR (OLD.processed_on = null AND NEW.processed_on = null)
    THEN    NEW.import_timestamp = now();
    ELSE 
        NEW.import_timestamp = OLD.import_timestamp;
    
END IF;
    RETURN NEW; 
END;
$body$
LANGUAGE 'plpgsql';


CREATE TRIGGER test_update_trg 
  BEFORE UPDATE 
  ON test
  
FOR EACH ROW 
  EXECUTE PROCEDURE trg_update_import_timestamp();
  
  update test set some_value  = 444 where id in (1,3);
select * from test order by 1

Upvotes: 1

Views: 1669

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

If I understand correctly, you want a new import_timestamp for rows 1 and 3. I believe this does what you want:

CREATE OR REPLACE FUNCTION trg_update_import_timestamp (
)
RETURNS trigger AS
$body$
BEGIN
    IF OLD.processed_on is not distinct from NEW.processed_on 
    THEN NEW.import_timestamp = now();
    ELSE NEW.import_timestamp = OLD.import_timestamp;
    
END IF;
    RETURN NEW; 
END;
$body$
LANGUAGE 'plpgsql';

Here is a db<>fiddle.

Upvotes: 3

Related Questions