时间戳

首页 > 解决方案 > `instead of update` trigger: How to distinguish when user provides new value for the column and when does not?

postgresql - `instead of update` trigger: How to distinguish when user provides new value for the column and when does not?

问题描述

Inside the trigger NEW.field value is always has <some value> despite on that column appears on SET list of UPDATE statement or does not:

UPDATE test SET name = 'abc', field = <some value>;
UPDATE test SET name = 'abc';

For both queries in trigger I will get field with <some value>.

Is there a way to distinguish these two queries from inside trigger?

标签： postgresql

解决方案

我认为您可以将新值与旧值进行比较。如果不同，您就知道该字段已设置。

除非您想捕获该字段的更新时间，否则即使该字段值没有更改，我也会认为这会捕获它。

CREATE OR REPLACE FUNCTION test_update_trigger()
  RETURNS trigger AS
$BODY$
BEGIN

  if (OLD.field is null and NEW.field is not null or
      OLD.field is not null and NEW.field is null or
      OLD.field != NEW.field) then
    -- do something
  end if;

  return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

推荐阅读