07 February 2008 13:59 tonyrogerson

Using a UDF in a CHECK constraint to check validity of History Windows (Start - End Date Windows)

There are often times where we need to hold history in the base table, for instance a commission rates table. Here is an example of checking the validity of the window without resorting to a trigger – I basically use a UDF (User Defined Function) on the CHECK constraint...

UDF needs to be created first or the CREATE TABLE will fail...

create function dbo.fn_check_sales_commission_window (

        @sales_commission_id int,

        @individual_id   int,

        @comm_start_date smalldatetime,

        @comm_end_date   smalldatetime )

returns varchar(3)

as

begin

    declare @status varchar(3)

 

    if exists (

        select *

        from sales_commission

        where individual_id = @individual_id

          and id <> @sales_commission_id

          and ( @comm_start_date between comm_start_date and coalesce( comm_end_date, @comm_start_date  )

             or @comm_end_date between comm_start_date and coalesce( comm_end_date, @comm_end_date ) )

        )

        set @status = 'BAD'

 

    else

        set @status = 'OK'

 

    return @status

 

end

Now create the table...

create table sales_commission (

    id  int not null identity constraint pk_sales_commission primary key clustered,

 

    individual_id  int not null,

 

    comm_rate decimal( 5, 2 ) not null,

 

    comm_start_date smalldatetime not null check( comm_start_date = cast( convert( char(8), comm_start_date, 112 ) as smalldatetime ) ),

    comm_end_date   smalldatetime null  check( comm_end_date is null or comm_end_date = cast( convert( char(8), comm_end_date, 112 ) as smalldatetime ) ),

        constraint uk_sales_commission unique ( individual_id, comm_start_date ),   

        constraint ck_sales_commission_window check( dbo.fn_check_sales_commission_window( id, individual_id, comm_start_date, comm_end_date ) = 'OK' )

)

go

Onto testing...

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date ) values ( 1, 20, '20080101', null )

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date ) values ( 1, 20, '20080511', null )  -- Fails

 

update sales_commission set comm_end_date = '20080510' where individual_id = 1      -- OK

 

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date ) values ( 1, 20, '20080511', null )  -- Now works

 

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date ) values ( 1, 20, '20070101', '20080201' ) -- Fails, End Date end up in range

 

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date ) values ( 1, 20, '20070101', '20071231' ) -- Works


Does it work when you are inserting a set?

truncate table sales_commission

 

 

insert sales_commission( individual_id, comm_rate, comm_start_date, comm_end_date )

    select 1, 20, '20080101', null

    union all

    select 1, 20, '20080511', null  -- This one is wrong and should cause a Failure


It does! Cool.

I'm not going to get into maintenance of the CHECK CONSTRAINT because that will spoil the fun, anyway if you try and update the UDF you rightly get this error...

Msg 3729, Level 16, State 3, Procedure fn_check_sales_commission_window, Line 24

Cannot ALTER 'dbo.fn_check_sales_commission_window' because it is being referenced by object 'ck_sales_commission_window'.


To get round this you need to drop the contraint first and use ALTER TABLE to put it back on.

Be warned that the UDF is executed row by row (for each row inserted or updated as they get updated/inserted) so an inconsistency may occur if you are aggregating rather than just doing existance checking like in my specific business case - heres an example that shows the update is applied row by row and that the CHECK is done row by row, so the update is not complete...

create table checks (

    flag char(1) not null

)

 

insert checks values( 'A' )

insert checks values( 'A' )

insert checks values( 'A' )

insert checks values( 'A' )

insert checks values( 'A' )

 

go

 

create function dbo.fn_check ( @flag char(1) )

    returns varchar(3)

as

begin

    declare @status varchar(3)

 

    if exists ( select * from checks where flag <> @flag )

        set @status = 'BAD'

    else

        set @status = 'OK'

 

    return @status

 

end

go

 

alter table checks add constraint checks_udf_lookup check ( dbo.fn_check( flag ) = 'OK' )

go

 

If we had transaction consistency then this should work fine because all rows would be updated to 'B' at once and then the check constraint executed; alas, it doesn't; because what is happening is that sql updates row 1, checks it, updates row 2, checks it etc...

 

update checks

    set flag = 'B'

 

One last point, you notice I made it a table level CHECK constraint - it needs to be table level so all the columns are available to put in the parameter list.

Tony.

Filed under:

Comments

# re: Using a UDF in a CHECK constraint to check validity of History Windows (Start - End Date Windows)

07 February 2008 16:07 by Alex_Kuznetsov

Hi Tony,

That's a very interesting post. I recently worked on a similar problem and came up with a different solution. I will post it soon.

# How to Find Overlapping Date Ranges in a Table | keyongtech

Pingback from  How to Find Overlapping Date Ranges in a Table | keyongtech

# Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

26 June 2009 04:01 by Alexander Kuznetsov

Surely this sounds like common knowledge, yet I decided to post a simple benchmark demonstrating how

# A constraint that only allows one of two tables to reference a base table. - Programmers Goodies

Pingback from  A constraint that only allows one of two tables to reference a base table. - Programmers Goodies

# How are my SQL Server constraints being bypassed? - Programmers Goodies

Pingback from  How are my SQL Server constraints being bypassed? - Programmers Goodies

# How Are My SQL Server Constraints Being Bypassed? | Click &amp; Find Answer !

Pingback from  How Are My SQL Server Constraints Being Bypassed? | Click &amp; Find Answer !