In SQL Server 2005 you can write a check constraint that can include a function call. For example:
ALTER TABLE [dbo].[TestTable1]
ADD CONSTRAINT [TestConstraint1]
CHECK ([dbo].[TestFunction1]([PrimaryKeyColumn1],[Column1],[Column2]) = 0)
However if the function you are calling tests the contents of that table you must be aware that the row you are trying to insert or update will already be in the table. Therefore if part of the test is, for example, duplicate checking or date range checking (in the case of writing scheduling software) then you must pass into the function the primary key of the row you are inserting and add that as a where clause to your function:
CREATE FUNCTION [dbo].[TestFunction1]
(
@param1 int,
@param2 int,
@param3 int,
)
RETURNS bit
AS
BEGIN
DECLARE @ClashesFound int
DECLARE @Result bit
SET @ClashesFound = 0
SET @Result = 0
SET @ClashesFound = (
SELECT COUNT(*)
FROM [dbo].[TestTable1]
WHERE
[PrimaryKeyColumn1] <> @param1
)
IF (@ClashesFound > 0)
SET @Result = 1
RETURN @Result
END
Check constraints that access the table you are inserting into
1
Still, nice to wake up to all those hot air balloons. More photos here:
