A question about the number of times a trigger will fire

Started by stanabout 6 years ago3 messagesgeneral
Jump to latest
#1stan
stanb@panix.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: stan (#1)
Re: A question about the number of times a trigger will fire

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

David J.

#3stan
stanb@panix.com
In reply to: David G. Johnston (#2)
Re: A question about the number of times a trigger will fire

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