Calling UDF from constraint
-
I am using SQL Server 2000 Standard ed, SP4. I have created a constraint on a table that calls a user defined function to restrict duplicate values from being inserted or updated on a column in the table tblOrders.
The UDF is defined as:
CREATE FUNCTION dbo.fnCheckCBCaseNum (@CBCaseNum varchar(50))
RETURNS int
AS
BEGIN
DECLARE @ReturnValue int
IF EXISTS (SELECT * FROM tblOrder WHERE CBCaseNum = @CBCaseNum)
SET @ReturnValue = 1
ELSE SET @ReturnValue = 0
RETURN @ReturnValue
END
The Table has one column CBCaseNum.
The constriant is defined as:
CONSTRAINT [CK_tblOrder] CHECK ([dbo].[fnCheckCBCaseNum]([CBCaseNum]) = 0)
I want the above constraint to restrict records where the CBCaseNum already exists in the table. Unfortunately, the UDF appears to always be returning one return value when called from the constraint. When called normally in QA, it returns the expected return value.
For example, if CaseNum = '1' and it exists in the table, this returns 1 as expected:
SELECT dbo.fnCheckCBCaseNum('1')
However, if I try to insert another record w/ a 1, the constraint does not stop the record from being inserted:
BEGIN tran
INSERT INTO tblOrder (CBCaseNum) VALUES ('1')
Commit tran
If I try to insert another CaseNum = '2' and it doesn't exists in the table, this one does get inserted as expected and the UDF returns the correct value of 0:
SELECT dbo.fnCheckCBCaseNum('2')
BEGIN tran
INSERT INTO tblOrder (CBCaseNum) VALUES ('2')
Commit tran
My co-workers and I have tried many different ideas including re-writing the UDF and modifying the constraint in many different ways, but it always results in the same issue. Either it works for one situation, but not the other. If anyone can shed any light on what I may be doing incorrectly or if perhaps this is a known issue in SQL Server 2000, i would greatly appreciate it.
Thanks,
Lisa
-
Why can't you just use a UNIQUE constraint to do this?
-
Good point, but, the UDF is more complicated than my example. It needs to only kick out dups if the order meets a certain criteria.
For example, my select joins on another table and if I have a field in that table flagged as TRUE, then I want the constraint to not allow inserts/updates for that record.
So my IF EXISTS in the UDF would be something similar to this:
IF EXISTS (SELECT * FROM tblOrder JOIN tblPayment ON tblOrder.OrderNo = tblPayment.OrderNo
WHERE CBCaseNum = @CBCaseNum
AND DupCaseNum = 1)
-
Put your UDF logic into a stored procedure and only allow inserts to happen through calls to that SP or use an INSTEAD OF INSERT trigger on the table.
-
Those are both possible solutions, but does anyone know why using a CHECK constraint that calls a UDF wouldn't work? Perhaps I am calling it incorrectly or something else is wrong? I have seen various examples online at and it is documented in BOL and various other resources as being possible.
-
This works for me. Maybe that will put you on the right track!!
USE SSC
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'UDFDemo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.UDFDemo
GO
CREATE TABLE dbo.UDFDemo
(
CBCaseNum VARCHAR(50) NOT NULL
)
GO
CREATE CLUSTERED INDEX CIX_UDFDemo_CBCaseNum ON dbo.UDFDemo (CBCaseNum)
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'fnCheckCBCaseNum' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')
DROP FUNCTION dbo.fnCheckCBCaseNum
GO
CREATE FUNCTION dbo.fnCheckCBCaseNum (@CBCaseNum AS VARCHAR(50))
RETURNS INT
AS
BEGIN
RETURN CASE WHEN EXISTS (SELECT * FROM dbo.UDFDemo WHERE CBCaseNum = @CBCaseNum) THEN 1 ELSE 0 END
END
GO
INSERT INTO dbo.UDFDemo (CBCaseNum) VALUES ('1')
GO
SELECT dbo.fnCheckCBCaseNum('1') AS True1, dbo.fnCheckCBCaseNum('0') AS False0
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoCnst' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.DemoCnst
GO
CREATE TABLE dbo.DemoCnst
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
, CBCaseNum VARCHAR(50) NOT NULL CONSTRAINT CK_DemoCnst_CBCaseNum CHECK (dbo.fnCheckCBCaseNum([CBCaseNum]) = 0)
)
GO
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('0') --this should always work (because there's no constraint for the DemoCnst Table
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('1') --Will always fail
GO
SELECT * FROM dbo.DemoCnst
-
Thanks for all your help SSC, but unfortunately the solution you posted is missing one criteria inside the UDF. I am not comparing values soley, but also making sure that if the additional flag value is true for that casenum, it fires the constraint. Therefore, the UDF is defined as something like this:
So my IF EXISTS in the UDF would be something similar to this:
IF EXISTS (SELECT * FROM tblOrder JOIN tblPayment ON tblOrder.OrderNo = tblPayment.OrderNo
WHERE tblOrder.CBCaseNum = @CBCaseNum
AND tblPayment.DupCaseNum = 1)
Any other suggestions?
-
Ya change my exists to yours. The rest of the logic is all the same whether you use on exists statement or another.
-
I tried that, still isn't working:
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'UDFDemo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.UDFDemo
GO
CREATE TABLE dbo.UDFDemo
(
CBCaseNum VARCHAR(50) NOT NULL
)
GO
CREATE CLUSTERED INDEX CIX_UDFDemo_CBCaseNum ON dbo.UDFDemo (CBCaseNum)
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'fnCheckCBCaseNum' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')
DROP FUNCTION dbo.fnCheckCBCaseNum
GO
CREATE FUNCTION dbo.fnCheckCBCaseNum (@CBCaseNum AS VARCHAR(50))
RETURNS INT
AS
BEGIN
RETURN CASE WHEN EXISTS (SELECT * FROM dbo.UDFDemo JOIN dbo.DemoCnst ON dbo.UDFDemo.CBCaseNum = dbo.DemoCnst.CBCaseNum WHERE dbo.DemoCnst.CBCaseNum = @CBCaseNum AND dbo.UDFDemo.CBCaseNum = 1) THEN 1 ELSE 0 END
END
GO
INSERT INTO dbo.UDFDemo (CBCaseNum) VALUES ('1')
GO
SELECT dbo.fnCheckCBCaseNum('1') AS True1, dbo.fnCheckCBCaseNum('0') AS False0
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoCnst' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.DemoCnst
GO
CREATE TABLE dbo.DemoCnst
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
, CBCaseNum VARCHAR(50) NOT NULL CONSTRAINT CK_DemoCnst_CBCaseNum CHECK (dbo.fnCheckCBCaseNum([CBCaseNum]) = 0)
)
GO
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('0') --this should always work (because there's no constraint for the DemoCnst Table
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('1') --Will always fail
GO
SELECT * FROM dbo.DemoCnst
SELECT * FROM udfdemo
-
My understanding is incomplete either in the logic or in the error you are getting. Can you add some comments in the code you just poted so I can help you out further?
-
I believe the "order" of the statements should be rearranged as :
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoCnst' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.DemoCnst
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'fnCheckCBCaseNum' AND XType = 'FN' AND USER_NAME(uid) = 'dbo')
DROP FUNCTION dbo.fnCheckCBCaseNum
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'UDFDemo' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.UDFDemo
GO
CREATE TABLE dbo.UDFDemo
(
CBCaseNum VARCHAR(50) NOT NULL
)
GO
CREATE CLUSTERED INDEX CIX_UDFDemo_CBCaseNum ON dbo.UDFDemo (CBCaseNum)
GO
CREATE FUNCTION dbo.fnCheckCBCaseNum (@CBCaseNum AS VARCHAR(50))
RETURNS INT
AS
BEGIN
RETURN CASE WHEN EXISTS (SELECT * FROM dbo.UDFDemo JOIN dbo.DemoCnst ON dbo.UDFDemo.CBCaseNum = dbo.DemoCnst.CBCaseNum WHERE dbo.DemoCnst.CBCaseNum = @CBCaseNum AND dbo.UDFDemo.CBCaseNum = 1) THEN 1 ELSE 0 END
END
GO
INSERT INTO dbo.UDFDemo (CBCaseNum) VALUES ('1')
GO
CREATE TABLE [dbo].[DemoCnst] (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
, CBCaseNum VARCHAR(50) NOT NULL CONSTRAINT CK_DemoCnst_CBCaseNum CHECK (dbo.fnCheckCBCaseNum([CBCaseNum]) = 0)
)
GO
SELECT dbo.fnCheckCBCaseNum('1') AS True1, dbo.fnCheckCBCaseNum('0') AS False0
GO
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('0') --this should always work (because there's no constraint for the DemoCnst Table
INSERT INTO dbo.DemoCnst (CBCaseNum) VALUES ('1') --Will always fail
GO
SELECT * FROM dbo.DemoCnst
SELECT * FROM udfdemo
But it looks correct to me too
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply