I'm new to triggers, so please bear with me.

Is there a way to create a trigger which will ensure that a a newly-added or
updated column has either a NULL value or else a unique value? This is to
model a value which may not be known, but if it is known, then it has to be
unique.

I tried to create  one like this:


CREATE TRIGGER Unique_ColumnX
ON dbo.MyTable
FOR INSERT, UPDATE
AS
If UPDATE(ColumnX)
BEGIN
IF EXISTS (SELECT i.ColumnX FROM inserted i INNER JOIN MyTable m ON
i.ColumnX= m.ColumnX)
BEGIN
RAISERROR ('Duplicate ColumnX found', 16, 1)
RETURN
END
END


My problem is that I'm not sure how this behaves when multiple rows are
inserted or updated (or, in fact, whether this is a good idea at all!)

Thanks.
--
John Saunders
John.Saunders at SurfControl.com

[ Post a follow-up to this message ]

John
CREATE TABLE multinulls (
a int NULL,
b char(5) default 'hello'
)

GO
CREATE VIEW v_multinulls
WITH SCHEMABINDING
AS
SELECT a FROM dbo.multinulls
WHERE a IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)
GO

INSERT INTO multinulls(a) VALUES(1)

-- Generates error because of duplicate!
INSERT INTO multinulls(a) VALUES(1)

INSERT INTO multinulls(a) VALUES(2)
INSERT INTO multinulls(a) VALUES(3)
INSERT INTO multinulls(a) VALUES(NULL)

-- No error
INSERT INTO multinulls(a) VALUES(NULL)

-- No error
INSERT INTO multinulls(a) VALUES(NULL)
GO

SELECT a, b FROM multinulls
GO

DROP VIEW v_multinulls
DROP TABLE multinulls
GO


"John Saunders" <john.saunders at SurfControl.com> wrote in message
news:OUrivpdBEHA.1452@TK2MSFTNGP09.phx.gbl...
> I'm new to triggers, so please bear with me.
>
> Is there a way to create a trigger which will ensure that a a newly-added
or
> updated column has either a NULL value or else a unique value? This is to
> model a value which may not be known, but if it is known, then it has to
be
> unique.
>
> I tried to create  one like this:
>
>
> CREATE TRIGGER Unique_ColumnX
> ON dbo.MyTable
> FOR INSERT, UPDATE
> AS
> If UPDATE(ColumnX)
> BEGIN
>  IF EXISTS (SELECT i.ColumnX FROM inserted i INNER JOIN MyTable m ON
> i.ColumnX= m.ColumnX)
>  BEGIN
>   RAISERROR ('Duplicate ColumnX found', 16, 1)
>   RETURN
>  END
> END
>
>
> My problem is that I'm not sure how this behaves when multiple rows are
> inserted or updated (or, in fact, whether this is a good idea at all!)
>
> Thanks.
> --
> John Saunders
> John.Saunders at SurfControl.com
>
>

[ Post a follow-up to this message ]

Since your trigger fires after the insert, it will detect duplicate rows
(non-null) even when none exist.  Also, you have no ROLLBACK so the insert
will succeed even when the RAISERROR is executed.

CREATE TRIGGER Unique_ColumnX
ON dbo.MyTable
FOR INSERT, UPDATE
AS
If UPDATE(ColumnX)
BEGIN
IF (SELECT COUNT(*)
FROM inserted i
INNER JOIN MyTable m ON
i.ColumnX= m.ColumnX
) > 1
BEGIN
RAISERROR ('Duplicate ColumnX found', 16, 1)
ROLLBACK
RETURN
END
END

If you are running SQL 2000, another technique is to create an indexed view
to enforce your unique-if-not-null constraint.  For example:

CREATE TABLE MyTable
(
MyPK INT NOT NULL,
ColumnX int NULL
)
GO

CREATE VIEW uv_MyTable
WITH SCHEMABINDING
AS
SELECT ColumnX
FROM dbo.MyTable
WHERE ColumnX IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uv_MyTable_cdx ON
uv_MyTable(ColumnX)
GO
INSERT INTO MyTable VALUES(1, NULL)
INSERT INTO MyTable VALUES(2, NULL)
INSERT INTO MyTable VALUES(3, 1)
INSERT INTO MyTable VALUES(4, 1) --fails
GO

Note that you'll need to have SET ARITHABORT ON in order to maintain the
view index.  This can be done from the client with an explicit SET
ARITHABORT ON, at the database level with ALTER DATABASE or at the server
level with sp_configure 'user options'.  See the Books Online for more
information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Saunders" <john.saunders at SurfControl.com> wrote in message
news:OUrivpdBEHA.1452@TK2MSFTNGP09.phx.gbl...
> I'm new to triggers, so please bear with me.
>
> Is there a way to create a trigger which will ensure that a a newly-added
or
> updated column has either a NULL value or else a unique value? This is to
> model a value which may not be known, but if it is known, then it has to
be
> unique.
>
> I tried to create  one like this:
>
>
> CREATE TRIGGER Unique_ColumnX
> ON dbo.MyTable
> FOR INSERT, UPDATE
> AS
> If UPDATE(ColumnX)
> BEGIN
>  IF EXISTS (SELECT i.ColumnX FROM inserted i INNER JOIN MyTable m ON
> i.ColumnX= m.ColumnX)
>  BEGIN
>   RAISERROR ('Duplicate ColumnX found', 16, 1)
>   RETURN
>  END
> END
>
>
> My problem is that I'm not sure how this behaves when multiple rows are
> inserted or updated (or, in fact, whether this is a good idea at all!)
>
> Thanks.
> --
> John Saunders
> John.Saunders at SurfControl.com
>
>

[ Post a follow-up to this message ]

Thanks, all!

--
John Saunders
John.Saunders at SurfControl.com


"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:e2S9o7dBEHA.2308@tk2msftngp13.phx.gbl...
> Since your trigger fires after the insert, it will detect duplicate rows
> (non-null) even when none exist.  Also, you have no ROLLBACK so the insert
> will succeed even when the RAISERROR is executed.
>
> CREATE TRIGGER Unique_ColumnX
> ON dbo.MyTable
> FOR INSERT, UPDATE
> AS
> If UPDATE(ColumnX)
> BEGIN
>     IF (SELECT COUNT(*)
>         FROM inserted i
>         INNER JOIN MyTable m ON
>         i.ColumnX= m.ColumnX
>         ) > 1
>     BEGIN
>         RAISERROR ('Duplicate ColumnX found', 16, 1)
>         ROLLBACK
>         RETURN
>     END
> END
>
> If you are running SQL 2000, another technique is to create an indexed
view
> to enforce your unique-if-not-null constraint.  For example:
>
> CREATE TABLE MyTable
>     (
>     MyPK INT NOT NULL,
>     ColumnX int NULL
>     )
> GO
>
> CREATE VIEW uv_MyTable
> WITH SCHEMABINDING
> AS
> SELECT ColumnX
> FROM dbo.MyTable
> WHERE ColumnX IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uv_MyTable_cdx ON
> uv_MyTable(ColumnX)
> GO
> INSERT INTO MyTable VALUES(1, NULL)
> INSERT INTO MyTable VALUES(2, NULL)
> INSERT INTO MyTable VALUES(3, 1)
> INSERT INTO MyTable VALUES(4, 1) --fails
> GO
>
> Note that you'll need to have SET ARITHABORT ON in order to maintain the
> view index.  This can be done from the client with an explicit SET
> ARITHABORT ON, at the database level with ALTER DATABASE or at the server
> level with sp_configure 'user options'.  See the Books Online for more
> information.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "John Saunders" <john.saunders at SurfControl.com> wrote in message
> news:OUrivpdBEHA.1452@TK2MSFTNGP09.phx.gbl... 
newly-added
> or 
to 
> be 
>
>

[ Post a follow-up to this message ]