Обсуждение: A question about the number of times a trigger will fire
I am in the process of trying to set up a function.trigger pair to create functionally an updateable view. I would think that the trigger would fire the number of times that the calling statement's WHERE clause seceded. Is this incorrect> I have a view called purchase_view, one of the tables in it's join is a table called bom_item. I have defined this trigger: REATE TRIGGER test_v_trig INSTEAD OF INSERT OR UPDATE ON purchase_view FOR EACH ROW EXECUTE PROCEDURE v_trig_test(); Prior to firing this trigger this query select count(*) FROM purchase_view WHERE proj_no = 3124 AND m_name = 'Mencom' ; returns 11 The resultant statement generated by the function called by this trigger is: UPDATE BOM_ITEM SET cost_per_unit = 23.45 , qty = 12345.00 Which in retrospect dies not do what I had in mind :-) So, it appears that I need to create a WHERE clause for the resultant statement. But I do not see how the function has enough data to use to create this where clause. What am I missing, here? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Thu, Mar 5, 2020 at 7:58 AM stan <stanb@panix.com> wrote:
UPDATE BOM_ITEM SET cost_per_unit = 23.45 , qty = 12345.00
So, it appears that I need to create a WHERE clause for the resultant
statement. But I do not see how the function has enough data to use to
create this where clause.
What am I missing, here?
OLD and/or NEW?
David J.
On Thu, Mar 05, 2020 at 08:58:32AM -0700, David G. Johnston wrote: > On Thu, Mar 5, 2020 at 7:58 AM stan <stanb@panix.com> wrote: > > > UPDATE BOM_ITEM SET cost_per_unit = 23.45 , qty = 12345.00 > > > > So, it appears that I need to create a WHERE clause for the resultant > > statement. But I do not see how the function has enough data to use to > > create this where clause. > > > > What am I missing, here? > > > > > OLD and/or NEW? > > https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER Yes, I was thinking that might be what I had to do. My thinking right now is to create a WHERE clause for the new statement, using (perhaps a subset) of the columns returned by this. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin