January 14, 2009 at 12:29 am
Hello,
I am creating a UDF to enforce a business rule:
CREATE FUNCTION [dbo].[UDF_IsValidEntryDate]
(@Record_Id Int, @Entry_Date SmallDateTime)
RETURNS Bit
AS
BEGIN
DECLARE @btValidEntry As Bit
SELECT @btValidEntry = 0
IF NOT Exists
(
SELECT * FROM myTable
WHERE Record_Id = @Record_ID AND
(@Entry_Date BETWEEN [FROM] AND [TO])
)
BEGIN
SELECT @btValidEntry = 1
END
RETURN @btValidEntry
END
Then I am creating a the following check constraint:
(
[dbo].[UDF_IsValidEntryDate]([Contract_Id], [FROM]) = Convert(Bit,1)
)
AND
(
[dbo].[UDF_IsValidEntryDate]([Contract_Id], [TO]) = Convert(Bit,1)
)
The constraint is created successfully, however, when trying to insert new records the constraint fails eventhough the entered values are Ok. Also when calling the function from the query editor it functions properly.
Any idea?
Thanks,
January 14, 2009 at 4:46 am
I think there must be something wrong with the data. I could have helped you a bit more if you provide some more information about the table structure and the actual data stored.
--Ramesh
January 14, 2009 at 6:09 am
Table structure:
[Record_Id] [int] NOT NULL,
[From] [smalldatetime] NOT NULL,
[To] [smalldatetime] NOT NULL
Data:
Record_Id FROM To
12008-01-02 00:00:002008-01-02 00:00:00
12009-01-02 00:00:002009-01-02 00:00:00
112008-01-01 00:00:002008-12-31 00:00:00
112009-01-01 00:00:002009-12-31 00:00:00
112010-01-01 00:00:002010-12-31 00:00:00
162008-04-01 00:00:002009-03-31 00:00:00
162009-04-01 00:00:002010-03-31 00:00:00
162010-04-01 00:00:002011-03-31 00:00:00
162011-04-01 00:00:002012-03-31 00:00:00
162012-04-01 00:00:002013-03-31 00:00:00
Thanks,
January 14, 2009 at 8:00 am
What is your business rule?
I have always used 1 to represent success and 0 for fail as the return value in a user defined function used as a check constraint.
Is it your intention that when the entry date is between the FROM and TO for a row that matches your Record_ID then this is valid and the rule is satisfied?
Si
January 14, 2009 at 8:03 am
The problem is because the check constraint is checked after the insert. At that time it finds a record in the table and rollback the transaction. You’ll need to modify your function so it will count the number of records according to the dates and ID and if it finds 1 record it is O.K. In case it finds more then 1 record, it will not pass the check.
I’ve noticed that you used key words as column names. I suggest that you’ll avoid using key words as column names. It just leads to problems and have no advantages.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 8:09 am
CHECK constraints happen BEFORE the command occurs. It is triggers that happen AFTER the command occurs, hence why CHECK CONSTRAINTS are recommended over triggers when you are dealing with checking columns in a single table.
Si
January 14, 2009 at 9:19 am
SimonD (1/14/2009)
CHECK constraints happen BEFORE the command occurs. It is triggers that happen AFTER the command occurs, hence why CHECK CONSTRAINTS are recommended over triggers when you are dealing with checking columns in a single table.Si
Pleas take a look at the script bellow and play with it. I think it shows that the check constraint is being checked after the record was inserted into the table and not before (at least in some cases).
--Create a function that there are no records with the same
--in the table with the same ID as the new record.
create function CheckExistance (@id int)
returns int
as
begin
declare @i int
if exists(select * from DemoTable where id = @id)
set @i = 1 --Found record in the table with the same ID
else
set @i = 0 --There are no records in the table with the same ID
return @i
end
go
--Create the table and use the function in the check constraint
create table DemoTable (
id int not null CONSTRAINT CHK_DemoTable check(dbo.CheckExistance(id)=0))
go
--Since this is a new table, there are no records in it, but the insert failes.
--In my opinion because the check constraint is checked AFTER the record was inserted
insert into DemoTable (id) values (1)
go
--At this point I'll modify the function. The function will check
--that the table has exactly 1 record with the same ID as
--the ID that I'm inserting
--First I have to drop the constraint, so I'll be able to
--modifry the function.
alter table DemoTable drop constraint CHK_DemoTable
go
--Altering the table so it will count the number of records.
--
alter function CheckExistance (@id int)
returns int
as
begin
declare @i int
if ((select count(*) from DemoTable where id = @id) = 1)
set @i = 0 --There is exactly 1 record with the same ID as was inserted
else
set @i = 1 --There are no records or more then 1 record with the same ID that was inserted
return @i
end
go
--Alter the table and add the check constraint again
alter table DemoTable add constraint CHK_DemoTable check (dbo.CheckExistance(id)=0)
go
--Check that there are no records in the table.
if ((select count(*) from DemoTable) = 0)
select 'there are no records in the table'
else
select 'there are some records in the table'
--This works
insert into DemoTable (id) values (2)
--Check that the record was inserted
--second time fails
insert into DemoTable (id) values (2)
go
select * from DemoTable
go
drop table DemoTable
go
drop function CheckExistance
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 10:01 am
On SQL Server 2000 CHECK CONSTRAINTS were enforced "BEFORE" the insert actually took place apparently on 2005+ there are issues with this as you have already verified. There are several posts on connect related to issues with UDF in CHECK CONSTRAINTS that are using other rows in the table to perform table-level checks.
The PO should use an "INSTEAD OF" trigger to perform the check and avoid these issues.
* Noel
January 14, 2009 at 12:38 pm
Pleas take a look at the script bellow and play with it. I think it shows that the check constraint is being checked after the record was inserted into the table and not before (at least in some cases).
I have run the queries and have to admit that it does seem to be that CHECK constraints against columns invoking a UDF do indeed get applied AFTER the command has tried to INSERT or UPDATE the data! Your evidence appears to suggest this completely so I retract my earlier comment about that and hope this helps the original poster.
During my research I bumped into quite a few posts talking about CHECK CONSTRAINTS being satisfied if the column data on which the constraint is based can yield a NULL value. This is treated as "UNKNOWN" and the constraint accepts this as if the constraint condition were satisfied. Perhaps something else to watch out for.
Regards,
Si
January 14, 2009 at 12:59 pm
noeld (1/14/2009)
On SQL Server 2000 CHECK CONSTRAINTS were enforced "BEFORE" the insert actually took place apparently on 2005+ there are issues with this as you have already verified. There are several posts on connect related to issues with UDF in CHECK CONSTRAINTS that are using other rows in the table to perform table-level checks.The PO should use an "INSTEAD OF" trigger to perform the check and avoid these issues.
I just checked the script that I posted on SQL Server 2000 and it behaved the same way as it behaved on SQL Server 2005, so I’m not sure that with SQL Server 2000 the check constraint was checked before the insert (at least not at all cases).
I do agree with you that using instead of trigger and check for existence is much better then using the check constraint that is based on a UDF that counts the records.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 11:06 pm
Actually the businees rule needs to insure that only one record for a period of time, so if there is a record for the same period it will return 0 otherwise norecords exist so it's valid and returns 1.
January 14, 2009 at 11:19 pm
It's SQL Server 2005
April 13, 2009 at 4:04 am
I am using Sql Server 2005 and I had same problem,
But Adi is right, in Sql Server 2005 Check Constraint execute after command so in my function i use count(*) and check it values either > 0 or not,
but now i changed that condition to count(*) > 1 or not and it works fine
Thanks Adi
April 13, 2009 at 8:19 am
Nawar.Tabaa (1/14/2009)
Actually the businees rule needs to insure that only one record for a period of time, so if there is a record for the same period it will return 0 otherwise norecords exist so it's valid and returns 1.
When you say "only one record for a period of time", means the the combination of FROM and TO should be unique?
August 4, 2010 at 5:44 am
Thanks Adi! It resolved my issue!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply