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.