July 14, 2003 at 9:39 am
I have a business rule that I want to impliment in the database, since people make direct changes to the data. Basically I have a table defining how a nightly load is to be done, Full or Incrimental (F or I).
The table lists the info to load by a store_ID, and also includes a load begin date. So the table looks a little like this:
StoreID, LoadType, LoadBeginDate
UR63853, I, 1/1/03
IQHE823, F, 1/1/03
The business rule states simply: No more than one Full load in a night.
So I am trying to write a check constraint that will allow no more than one value of 'F' in the LoadType column. But I have not been successful yet.
Any Ideas - would a trigger be better?
July 14, 2003 at 1:01 pm
Create a UDF
Create myUDF(@LType, @LBeginDate)
Returns int
AS
BEGIN
Declare @Count int
IF LType ='F'
Select @Count=Count(*) from Table1 Where LoadType =@LType and
LoadBegindate=@LBegindate
ELSE
SET @Count=1
Return @Count
END
GO
Check Constrint on Table
Alter Table Table1 WITH NO_CHECK add Constraint CK_MYUDF Check(MyUDF(LoadType, LoadBeginDate)<=1)
Please check for sytax.
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
July 14, 2003 at 1:48 pm
Worked like a charm - I only made one change - I added the line
SET @count = @count + 1
Right after the select statement. Here's why: If we are switching to a Full load we need to include that in the count as well as the other full loads that exists.
Anyway - great solution. Thanks for the help!!
Jeff
July 14, 2003 at 1:55 pm
You wrote there can be maximum one full load per day.
I think that in your way it may accept 2 full loads
G.R. Preethiviraj Kulasingham
Chief Technology Officer.
Softlogic Information Systems Limited,
14 De Fonseka Place,
Colombo 05.
Sri Lanka.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy