Skip to main content
added 43 characters in body
Source Link
McNets
  • 23.9k
  • 11
  • 50
  • 89

You can check which parameters has changes in a single trigger:

create or replace function trg_sensor_data()
returns trigger as 
$$
begin

  if NEW.p1 <> OLD.p1 and NEW.p1 IS NOT NULL then
      NEW.p1_tm = now();
  end if;

  if NEW.p2 <> OLD.p2 and NEW.p2 IS NOT NULL  then
      NEW.p2_tm = now();
  end if;

  return NEW;
  
end;
$$
language 'plpgsql';
update sensor_data set p1 = 100;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | -: | :------------------ 1 | 100 | 2020-03-17 15:0818:1536.877775220044 | 23 | 2020-01-01 11:00:00

update sensor_data set p1 = 100, p2 = 200;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | --: | :------------------------- 1 | 100 | 2020-03-17 15:0818:1536.877775220044 | 200 | 2020-03-17 15:0818:1536.8801223565

db<>fiddle herehere

You can check which parameters has changes in a single trigger:

create or replace function trg_sensor_data()
returns trigger as 
$$
begin

  if NEW.p1 <> OLD.p1 then
      NEW.p1_tm = now();
  end if;

  if NEW.p2 <> OLD.p2 then
      NEW.p2_tm = now();
  end if;

  return NEW;
  
end;
$$
language 'plpgsql';
update sensor_data set p1 = 100;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | -: | :------------------ 1 | 100 | 2020-03-17 15:08:15.877775 | 23 | 2020-01-01 11:00:00

update sensor_data set p1 = 100, p2 = 200;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | --: | :----------------------- 1 | 100 | 2020-03-17 15:08:15.877775 | 200 | 2020-03-17 15:08:15.8801

db<>fiddle here

You can check which parameters has changes in a single trigger:

create or replace function trg_sensor_data()
returns trigger as 
$$
begin

  if NEW.p1 <> OLD.p1 and NEW.p1 IS NOT NULL then
      NEW.p1_tm = now();
  end if;

  if NEW.p2 <> OLD.p2 and NEW.p2 IS NOT NULL  then
      NEW.p2_tm = now();
  end if;

  return NEW;
  
end;
$$
language 'plpgsql';
update sensor_data set p1 = 100;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | -: | :------------------ 1 | 100 | 2020-03-17 15:18:36.220044 | 23 | 2020-01-01 11:00:00

update sensor_data set p2 = 200;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | --: | :------------------------- 1 | 100 | 2020-03-17 15:18:36.220044 | 200 | 2020-03-17 15:18:36.223565

db<>fiddle here

Source Link
McNets
  • 23.9k
  • 11
  • 50
  • 89

You can check which parameters has changes in a single trigger:

create or replace function trg_sensor_data()
returns trigger as 
$$
begin

  if NEW.p1 <> OLD.p1 then
      NEW.p1_tm = now();
  end if;

  if NEW.p2 <> OLD.p2 then
      NEW.p2_tm = now();
  end if;

  return NEW;
  
end;
$$
language 'plpgsql';
update sensor_data set p1 = 100;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | -: | :------------------ 1 | 100 | 2020-03-17 15:08:15.877775 | 23 | 2020-01-01 11:00:00

update sensor_data set p1 = 100, p2 = 200;

select * from sensor_data;

1 rows affected

id | p1 | p1_tm | p2 | p2_tm
-: | --: | :------------------------- | --: | :----------------------- 1 | 100 | 2020-03-17 15:08:15.877775 | 200 | 2020-03-17 15:08:15.8801

db<>fiddle here