Check Constraints using count.....

Question

Post reply

Check Constraints using count.....

jwt

Say Hey Kid

Points: 668
More actions
July 14, 2003 at 9:39 am

#83802

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?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 1

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/

jwt Say Hey Kid Points: 668 More actions · Answer 2

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

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 3

You wrote there can be maximum one full load per day.

I think that in your way it may accept 2 full loads