September 17, 2014 at 10:07 am
Hi All...
I am not even sure what to search under so here is the question:
Setup:
PStat
* PStat_id int (PK)
* Product_id int (FK)
* Onhand - Numeric (14,4)
InvLoc
* InvLoc_ID (PK)
* Loc_id (FK)
* Product_id (FK)
* Onhand - Numeric (14,4)
Issue:
I want to make a constraint or trigger that makes sure that the sum of InvLoc.onhand = PStat.onhand for the same product_id. If a process updates PStat but not any InvLoc records, the values become out of balance and should fail.
Any ideas?
Thanks,
Mike
September 17, 2014 at 11:02 am
In order to do this with a constraint you'd have to write a UDF that would allow you to check the data in the other table.
A trigger may be a better way to go with this. I'd even think that an INSTEAD OF trigger on PSTat that checks that the either the onHand value is not changed or that the change matches the InvLoc.OnHand value and if it doesn't, then don't make the change and raise and error using Throw (if you are on 2012).
Not knowing the business reason for storing the same data in 2 places, I'd also recommend that the business logic in the application (whether through stored procedures or in the application) handle this situation in addition to putting it in the database (defense in depth). If it really is a rule that those 2 columns must always have the same value then the business logic, wherever that resides, should enforce this rule by updating both columns in a single transaction and rolling back if one of the updates fails.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2014 at 1:01 pm
Use a trigger on InvLoc to keep the total in PStat up to date. No one should be updating the total in PStat directly if they need to be in sync.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply