I need to add some complex constraints at the DB.
For example.
Do not allow a line item of inventory to be changed if it does not result in the same number of joints originally shipped.
These will involve several tables.
What is the best approach for this?
Here is what I have been trying.
CREATE OR REPLACE FUNCTION numoriginaljts(genericitem_id bigint)
RETURNS double precision AS
'select coalesce(vsjo.diff,0) from inventory.t_generic_item gi
left join view.generic_item_shipment_id v on v.id=gi.id
left join v_shipment_jts_off vsjo on vsjo."shipmentId"=v.shipment_id
where gi.id=$1;'
LANGUAGE 'sql' VOLATILE
COST 100;
ALTER FUNCTION numoriginaljts(bigint) OWNER TO exploreco;
alter table inventory.t_generic_item add constraint check_shipment_original_jts CHECK (numoriginaljts(id)=0);
Does this approach seem reasonable?
This did not work, but it is probably my error. It actually let me break the constraint, but my constraint kicked in when I tried to correct the problem.
Can someone point me to an example of doing something like this? The point of this is to never let the total number of original pieces be different than the number originally shipped.
My code has done this occasionally and users can override the inventory.
Basically I would rather the application throw an error than let this number become unbalanced.
--
Thank you for your time,
Jason Long
CEO and Chief Software Engineer
BS Physics, MS Chemical Engineering
http://www.octgsoftware.com
HJBug Founder and President
http://www.hjbug.com