Uhh... the title of this is "Using a CHECK Constraint to Enforce a Trigger’s Unique Value" if you download the sample code, a trigger is being used... since when is a trigger the same thing as a CHECK constraint? wtf??

Phil4/27/2007 2:08:32 PM

--Forgot about updating to NULL. =Þ

CREATE TRIGGER tr_BaseTable_iud
ON BaseTable
FOR INSERT,
UPDATE,
DELETE

AS

/*
** æ 12/26/01 org: Trig to maintain unique values.
** æ 12/27/01 Bug fix: UPDATE to NULL.
*/

SET NOCOUNT ON

DELETE FROM ut
FROM dbo.UniqueTable ut
JOIN deleted d
ON ut.unique1 = d.unique1
LEFT JOIN inserted i
ON d.pkid = i.pkid
WHERE i.unique1 IS NULL

UPDATE ut
SET ut.unique1 = i.unique1
FROM dbo.UniqueTable ut
JOIN deleted d
ON ut.unique1 = d.unique1
JOIN inserted i
ON d.pkid = i.pkid
WHERE d.unique1 <> i.unique1
AND i.unique1 IS NOT NULL

INSERT INTO dbo.UniqueTable
SELECT i.unique1
FROM inserted i
LEFT JOIN deleted d
ON i.pkid = d.pkid
WHERE d.pkid IS NULL
AND i.unique1 IS NOT NULL

GO

Aaron Ellis 12/27/2001 9:01:46 AM

Although slightly more complicated, using a second table with a Unique index would help keep performance optimized regardless on the size on the base table:

CREATE TABLE BaseTable(
pkid int IDENTITY,
data1 varchar(10) NULL,
unique1 int NULL )
GO

ALTER TABLE BaseTable
ADD CONSTRAINT pk_BaseTable
PRIMARY KEY ( pkid)
GO

CREATE TABLE UniqueTable(
unique1 int NOT NULL )
GO

ALTER TABLE UniqueTable
ADD CONSTRAINT pk_UniqueTable
PRIMARY KEY ( unique1)
GO

CREATE TRIGGER tr_BaseTable_iud
ON BaseTable
FOR INSERT,
UPDATE,
DELETE

AS

SET NOCOUNT ON

DELETE FROM ut
FROM dbo.UniqueTable ut
JOIN deleted d
ON ut.unique1 = d.unique1
LEFT JOIN inserted i
ON d.pkid = i.pkid
WHERE i.pkid IS NULL

UPDATE ut
SET ut.unique1 = i.unique1
FROM dbo.UniqueTable ut
JOIN deleted d
ON ut.unique1 = d.unique1
JOIN inserted i
ON d.pkid = i.pkid
WHERE d.unique1 <> i.unique1

INSERT INTO dbo.UniqueTable
SELECT i.unique1
FROM inserted i
LEFT JOIN deleted d
ON i.pkid = d.pkid
WHERE d.pkid IS NULL
AND i.unique1 IS NOT NULL

GO

Aaron Ellis 12/26/2001 10:43:17 PM