UPDATE trigger function - SQL Server - Postgres equivalent

Question

SQL Server has this UPDATE function which one can use in triggers and which tells you whether a given column is part of the update/insert statement or not.

https://learn.microsoft.com/en-us/sql/t-sql/functions/update-trigger-functions-transact-sql?view=sql-server-2017

Does PostgreSQL have a logically equivalent function or any other way of checking this? Note that a logical check like old value <> new value is not what I need. Some update statements which I have contain code like

UPDATE table 
set 
col1 = col1
WHERE ...

and that has been done for a reason. I cannot easily change this.
So a logical check like old value <> new value would not work for me.

So I am looking just for an equivalent function in Postgres.

There is no such function in Postgres. And to be honest, I would question the design of a system that relies on this. Why isn't it enough to detect a change on the column value? — user330315
– user330315, Commented Dec 17, 2018 at 13:53
@a_horse_with_no_name I question the design too. But that's how it is. Don't ask me please :) Now we are migrating to Postgres and we can redesign it of course... but first I wanted to try to do it with minimal changes i.e. just by translating it logically equivalently to Postgres. — peter.petrov
– peter.petrov, Commented Dec 17, 2018 at 13:59

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You can try if specifying the column in the event of the trigger works for you.

CREATE TRIGGER reggirt
               BEFORE UPDATE OF nmuloc
                      ON elbat
               EXECUTE FUNCTION noitcnuf();

Note the OF ... after the UPDATE.

The documentation says:

For UPDATE events, it is possible to specify a list of columns using this syntax:
UPDATE OF column_name1 [, column_name2 ... ]
The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

Sounds like it could do what you want.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 17, 2018 at 14:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

peter.petrov Over a year ago

That sounds like a good idea. Is that the closest I can get in Postgres (to what I have in SQL Server) ? I guess it is.

peter.petrov Over a year ago

Tried it but it's not quite what I wanted... Seems there's no way to do it in Postgres.

Collectives™ on Stack Overflow

UPDATE trigger function - SQL Server - Postgres equivalent

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related