July 1, 2009 at 8:19 am
Hi Everyone
I have two tables that are in a one (Table1) to many (Table2) relationship. Both tables have From and To dates, and my application’s business rules are that the From and To date periods in Table2 must be within the From and To date of the parent record in Table1. For example, if the From and To dates in a record in Table1 are 1 January 2009 to 31 December 2011, the From and To dates of child records in Table2 must be (somewhere) between these dates.
On Table2 I have a check constraint (using a UDF that refers to Table1) to ensure that dates are valid, and this works fine when updating Table2 only. However, my application’s business rules are that when dates in Table1 are updated, some of the dates in Table2 also need updating. Continuing with the example above, when the To date of a record in Table1 gets changed, say, from 31 December 2011 to 31 December 2012, any child records in Table2 with a To date of 31 December 2011 also need to be changed to 31 December 2012. I’m currently doing the update in a transaction in a stored procedure, and here is the problem: The update on Table2 is rejected by the constraint, I think because the check constraint UDF is still seeing the To date on Table1 as 31 December 2011 (ie the original date), presumably because the transaction hasn’t committed.
I’m going round in circles trying to fix this. Can anyone point me in the right direction of a better / proper way of doing this?
All help greatly appreciated.
Many thanks - Graham
July 1, 2009 at 11:35 am
I would suggest using a trigger for a temproal constraint.
I assume you have additonal constraints for BeginDate < EndDate?
July 3, 2009 at 9:15 am
Thanks for this thought. I'm just off for a week's holiday so haven't had / got time to think this through, but can I tie a trigger in to my transaction?
(I have got constraints for ensuring that From is earlier than To.)
Cheers - Graham
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply