Thread: Determine in a trigger if UPDATE query really changed anything
Hi! 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. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On Sun, 23 Dec 2018 20:21:22 -0800, Mitar <mmitar@gmail.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
Hi! On Mon, Dec 24, 2018 at 12:31 AM George Neuner <gneuner2@comcast.net> wrote: > 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. I was thinking of the statement trigger because I thought this is what works on materialized views. Now I see that this is not true anyway. But still, I am using these triggers to do a materialized view refresh. I would prefer to do those at the statement level and not at the row level? So that I run the refresh only once per base table changes. For pushing notification this can be done at the row level. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m