**HAVE NULL AND NOT DUBLICATED VALUES IN COL**

Hi
I'm using SQL 2000,how is it possible to control a value of column to get
the ollowing result?
Emp (EmpId PK,EmpName,EmpCode)
EmpId EmpName EmpCode
-- -- --
100 John 3
101 Anne 5
102 Robert null
103 Jennifer null
104 Peter 78
I want to have some NULLs or a unique number in EmpCode column as above,
I tried to define Uniqe index or constraint but it wasn't successfull
because of null,
now how can I prevent of inserting a record of (105,'Tom',3) and not prevent
of inserting a record like (105,'Tom',null)
Any help would be greatly thankful.Here are three possible methods (in each case, Colx is the nullable,
unique column):
1. Use an indexed view:
CREATE VIEW SomeTable_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT colx
FROM dbo.SomeTable
WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
(colx)
2. Use an index with a calculated column:
CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
foo
AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
3. Use a trigger:
CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
ON SomeTable FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN SomeTable AS S
ON I.colx = S.colx AND I.keycol<>S.keycol)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate values not permitted',16,1)
END
David Portas
SQL Server MVP
--|||David
I think violation of UNIQUE KEY constraint will terminate the batch , i mean
we will not see the error generated by the trigger.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||I tried sth like below in the past ,but I think it can be dangrous in
sometimes
for example if we have a begin transaction in our statement and the this
trigger
fires and duplicated value was inserted then it will rollback to the poit
which made the other activities which are done before this trigger to be
rollbacked too!!!!!!!
what's your openion?
CREATE TRIGGER trg_SomeTable_Unique_Non_NULLred">
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
>
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:

>I tried sth like below in the past ,but I think it can be dangrous in
>sometimes
>for example if we have a begin transaction in our statement and the this
>trigger
>fires and duplicated value was inserted then it will rollback to the poit
>which made the other activities which are done before this trigger to be
>rollbacked too!!!!!!!
>what's your openion?
Hi Maryam,
Transactions are used to encapsulate a number of statements that should
either be executed as a whole, or none at all.
So if I have several statements and one of them is an insert that fails as
a result of duplicate data, I would expect and want SQL Server to undo all
changes made since the BEGIN TRANSACTION.
If you want to rollback only the insert but none of the preceding
activities, then simply commit the transaction for these other activities
first, before executing the insert.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,now imagine we don't define any begin trans before and we fire this
trigger and it will fail because of duplicated rows and rollback happen
too,but to which poit ?just to prevet inserting duplicated rows or......?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:13o811tiqus3gpgt2glj5tn74sp16utraa@.
4ax.com...
> On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:
>
> Hi Maryam,
> Transactions are used to encapsulate a number of statements that should
> either be executed as a whole, or none at all.
> So if I have several statements and one of them is an insert that fails as
> a result of duplicate data, I would expect and want SQL Server to undo all
> changes made since the BEGIN TRANSACTION.
> If you want to rollback only the insert but none of the preceding
> activities, then simply commit the transaction for these other activities
> first, before executing the insert.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||In that case only the INSERT operation itself will be rolled back.
--
David Portas
SQL Server MVP
--|||Yes, if you define colx as UNIQUE then the trigger will not fire. The
trigger is something you can use instead of a unique constraint.
David Portas
SQL Server MVP
--