Update Products QtyOnHand using Trigger on POReceipts table 1

[WantsToLearn]

I've read about triggers several times but now need to actually create one. Here is the scenario:

1. Receiving user pulls up PO Receiving form and enters qty received as appropriate for each detail line of the PO

2. When form is complete they click Update button and their entries are inserted into POReceipts detail table

3. I need to update Products table Qty_On_Hand with corresponding values from POReceipts detail table

I have the following questions:

1. Do I need to include commit and rollback logic in case it bombs in the middle

2. If I set up an Insert trigger on POReceipts then how do I pass that data to Products within the Update query (eg in the Update Products Qty_On_Hand Where Product_ID = , etc)

3. One of the threads I searched on before posting mentioned ensuring you handle the bulk insert situation in case something changes down the road - What is the best way to do that

Thanks for any ideas and/or suggestions.

 

[SQLBill]

1. Yes, you should include error handling. You don't need a commit (this isn't Oracle). Question to ask yourself, what needs to happen if the update fails? Include that in the error handling.

2. Write the UPDATE just as you would if it wasn't in a trigger.

UPDATE Qty_On_Hand
/*QOH field you want to update from the POR field*/
SET QOH.somefield = POR.somefield
FROM Qty_On_Hand QOH
JOIN POReceipt POR
/*QOH and POR fields that are used to join the two tables*/
ON QOH.somefield = POR.somefield

3. Bulk-Inserts. If you do a Bulk-Insert or Update on a table with a trigger, it can slow down your performance drastically. The normal solution is to disable the trigger(s) and do the updates manually from one table to the other.

-SQLBill

 

[WantsToLearn]

SQLBill,

Thanks for the response. I'm not sure how to add error checking and my question about bulk inserts had to do with something I saw in another thread about a common mistake new programmers make with triggers is assuming that they will be run in a one row at a time context. That is what I expect to happen here. Is there something else I should add in case someone else later makes a change somewhere which could impact this code?

Here's what I have so far and it is working OK in test mode:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/* Insert trigger for POReceipts */
CREATE TRIGGER UpdateProductsQtys
ON TblPOReceipts
FOR Insert Not For Replication AS
BEGIN
UPDATE dbo.Products
SET DateUpdated = getdate(),
Qty_On_Hand = Qty_On_Hand + Inserted.Quantity,
Qty_On_Order = Qty_On_Order - Inserted.Quantity
FROM dbo.Products Products,
dbo.TblPoDetails TblPoDetails,
Inserted
WHERE Products.ProductID = TblPoDetails.ProductID
AND TblPoDetails.PODet_ID = Inserted.PODet_ID
AND TblPoDetails.PO_ID = Inserted.PO_ID
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks!

 

[donutman]

If the PO Receiving form is within your programming domain, then I'd prefer a solution that updates the Product file from that application rather than using a trigger. But that's just MHO...for the most part, I don't do triggers or cursors.
-Karl

 

[WantsToLearn]

Hello donutman,

I'd be interested in your reasoning re: the above. My thoughts were that I should put the secondary updates in triggers to get them out of the UI, lessen traffic across the network, increase code security, etc.

The form is processing the PO line item details file via a linked table. Once the user clicks Update, there is form code which inserts records into the PO Receipts detail file and then the trigger updates the Products table.

I'm trying to minimize the amount of SQL code in the forms themselves. Am I missing something obvious here? Thanks!

 

[TJRTech]

Donutman, WantsToLearn...

If you can change the client, why not introduce a stored procedure that updates both tables. That reduces the network traffic to one round-trip, allows for programmatic error handling (transaction within, or compensating update/delete if second statement fails), and avoids the trigger.

None of our client app code is allowed to insert or delete directly...they all use SPs.

TR

 

[donutman]

I agree with TR. I was thinking of a seperate update, but his idea of combining an insert with an update is even better. SPs are the way to go, however, I don't think there is a strong case for NOT using triggers. I guess I just don't like the "hidden" nature of triggers and see no compelling reason to use them.
If you are coding in VB 6 with a bound control and using an ado row insert rather than a SP, then I can see why you might be attracted to a trigger solution because of its automatic nature, i.e. you don't have to do any more VB coding. It's basically a personal preference.
-Karl

 

[WantsToLearn]

Hello TJRTech,

The app has SQL Server on the back end and an Access .mdb on the front end with linked tables. I worked a little bit with SPs on the SQL Server side but have never had to hook them from Access. I can see your point about using a SP, then at least there is a reference to it in the form code so you know to take it into account when making changes later.

What is the best way to do this on the Access side? For purposes our discussion, let's say the SP takes 2 input parms which are both int. Data to be passed to the SP comes from unbound controls on the Access form.

Do I do something like the following using ADO pseudocode:

1. Pass parameters using parameters collection
2. Pass SP name using cmd.Text ?? property
3. Run it all with cmd.Execute

If someone can point me, I'll figure it out but a couple blurbs of SP code with the corresponding Access call would be greatly appreciated. Client is very small and on a tight budget so most of what I am doing here is building good will for future business. Thanks for any ideas!

 

[donutman]

I use VB (not VB script) and it goes like this:
1) Create cmdObject
2) cmdObject.connection = cnnObject
3) cmdObject.type=vbStoredProc
4) cmdObject.text="SP Name"
5) cmdObject.parameter.refresh
6) cmdObject.parameters(1)=j and (2)=k etc
7) cmdObject.execute
-Karl

 

[WantsToLearn]

Thanks donutman! That was exactly what I needed, something to get me started! I'll try that sometime tomorrow. THANKS!