PostgreSQL – How to Create Trigger to Check and Change Field Values

postgresqltrigger

I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far. 

CREATE TRIGGER check_Load
AFTER UPDATE ON PODIUM_CORE
FOR EACH ROW ( WHERE PODIUM_CORE.SNAME OR PODIUM_CORE.INTERNAL = "LOAD")
UPDATE PODIUM_CORE.SNAME = "LOAD_GENERAL";

EXECUTE PROCEDURE check_Load();

Best Answer

As documented in the manual a trigger in Postgres consists of a trigger function and a trigger definition.

String constants are written in single quotes in SQL, "LOAD" refers to a column or table name, 'LOAD' is a string (character) constant.

If you want to change something using a trigger, you should use a BEFORE trigger and simply assign the new value to the record. Running a separate DML statement for that is the wrong approach in Postgres.

You didn't really give us much to go with, but I assume you are looking for something like this:

Define the trigger function:

create function check_load() 
  returns trigger
as
$$
begin
  new.sname := 'LOAD_GENERAL';
  return new;
end;
$$
language plpgsql;

Then create the trigger:

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW 
  WHEN sname = 'LOAD' OR internal 'LOAD'
  EXECUTE PROCEDURE check_load();

It's more efficient to only fire the trigger when the specified values are provided, rather than firing the trigger always and check for the value inside the trigger.

But if you want to do that check inside the trigger function it would be something like this:

create function check_load() 
  returns trigger
as
$$
begin
  if (new.sname = 'LOAD' or new.internal = 'LOAD') then 
    new.sname := 'LOAD_GENERAL';
  end if;
  return new;
end;
$$
language plpgsql;

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW 
  EXECUTE PROCEDURE check_load();
Related Question