Re: Determine in a trigger if UPDATE query really changed anything

On Sun, 23 Dec 2018 20:21:22 -0800, Mitar <mmitar(at)gmail(dot)com> wrote:

>Currently I am doing:
>
>CREATE TRIGGER some_trigger AFTER UPDATE ON my_table REFERENCING NEW
>TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
>FUNCTION my_trigger();
>
>In my trigger I do:
>
>PERFORM * FROM ((TABLE new_table EXCEPT TABLE new_table) UNION ALL
>(TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
>IF FOUND THEN
> ...
>
>But I wonder if there is an easier way. I would just like to know if
>an UPDATE really changed anything.
>
>For DELETE I do "PERFORM * FROM old_table LIMIT 1" and for INSERT I
>do "PERFORM * FROM new_table LIMIT 1" and I think this is reasonable.
>Still, not sure why I have to store the whole relation just to know if
>statement really changed anything.

You're still thinking in terms of deltas for the whole table. Think
individual rows instead.

With a BY ROW trigger, the difference between the NEW and OLD
variables lets you see the changes to the particular row.

George