0

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();
1

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.