0

I have a table with 50 columns. I need to make a trigger to do something, i can do that, but i need to do whene 1 of 24 columns are updated. The problem is i have to do 24 times if NEW.column <> OLD.column THEN ELSEIF NEW.column2 <> OLD.column2 THEN .... I was wondering is there other (easy) way to do this check?

Thanks.

Improve this question
2
  • You could try to create a "AFTER UPDATE" trigger. This trigger fires on every update in the table, not just if one column changes. I do not know wich DBMS you are using, but PostgreSQL has this: postgresqltutorial.com/postgresql-triggers/… In PostgreSQL, a trigger is a database object that is fired automatically when an event such as INSERT, UPDATE, DELETE, or TRUNCATE occurs.
    – Martie
    Commented Apr 19 at 10:24
  • Thenks MArtie, on the title i wrote (postgres), but creating a trigger is not the problem, checking if one of 24 columns are changet is the problem.
    – RkassaTeam Breda
    Commented Apr 19 at 10:38

1 Answer 1

Reset to default
0

There is a specific syntax for this in PostgreSQL, see documentation:

CREATE TRIGGER trigger_name
AFTER UPDATE OF column_name1, column_name2, column_name3, column_name4, ..., column_name24 ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name;

By using OF column_name the trigger will only be called if that specific column changes.

Improve this answer
4
  • Thanks Sascha, as i wrote in my message, i can create triggers, i know how to do that, the problem is i dont want to have 24 times IF statements in the trigger, or to create 24 triggers for each column. I am looking some way to minimise this.
    – RkassaTeam Breda
    Commented Apr 21 at 13:03
  • @RkassaTeamBreda I understand. You can actually us the OF column_name syntax for multiple columns. I have updated my answer to reflect that, that should work to only create one trigger for 24 columns.
    – Sascha Mayr
    Commented Apr 22 at 14:05
  • Point of clarification, the "UPDATE OF" syntax only concerns if the column is referenced, not if the value has actually changed. So if you issue an update and set the column to the same value (or use an ORM that does that) then the trigger will fire even if there is no actual change in the value.
    – Jonathan Fite
    Commented Apr 22 at 15:24
  • hmm, thanks Jonathan, this i didnot know was possible, but unfortenatly it will not work for me becouse i need to know which column is changed, becouse i have to do differant things for each column.
    – RkassaTeam Breda
    Commented Apr 23 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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