Need help with the Trigger prevent duplicate value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with the Trigger prevent duplicate value

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-04-18 : 17:31:19

[code]Hi,

I need to create a trigger to prevent user enter duplicate values on these 3 columns below. I know it's easier to create unique index
on these columns but duplicate values already exist. They want to prevent insert duplicate values from now no. I try the trigger below but it does not work. Any help would greatly apprecate. Any other suggestion is welcome. Again, Thank you.
I am using SQL 2008.

IF OBJECT_ID('T1', 'u') IS NOT NULL
DROP TABLE T1
GO
CREATE TABLE T1
(
PKey INT NOT NULL PRIMARY KEY,
drawerno INT NULL,
DrawerStoreNo INT NULL,
DrawerRegisterNo INT NULL
)
GO

INSERT T1 VALUES (1, 0, 0, 0), (2, 0, 0, 0), (3, 0, 0, 0)
GO

SELECT *
FROM T1
GO

-- testing... Error.
CREATE UNIQUE INDEX unci_T1 ON dbo.T1 (drawerno, DrawerStoreNo, DrawerRegisterNo );
GO

-------------------------------------------------------------------------------

--Testing....
-- I should get an error duplicate values.
INSERT T1 VALUES (4, 0, 0, 0)
GO

-- Create trigger

DROP TRIGGER dbo.tr_ins_Phrmcst
GO

CREATE TRIGGER dbo.tr_ins_Phrmcst
ON Phrmcst
INSTEAD OF INSERT
AS
DECLARE @rowcnt INT

SET @rowcnt = @@ROWCOUNT

IF (@rowcnt = 0)
RETURN

IF EXISTS ( SELECT 1
FROM dbo.T1 AS a
JOIN INSERTED AS i
ON a.drawerno = i.drawerno
AND a.DrawerStoreNo = i.DrawerStoreNo
AND a.DrawerRegisterNo = i.DrawerRegisterNo )
BEGIN
RAISERROR ('Cannot enter duplicate value(s). Please check again...', 15, 1)
RETURN
END;
GO[/code]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-18 : 18:49:52

add unique constraint

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:25:13

so you want to preserve existing duplicate values or clear the existing duplicate occurances as well?
if former make use of WITH NO CHECK option while creating constraint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-04-19 : 11:56:31

Hi Visakhm,

For some reasons, they want to preserve data and preventing future inserting duplicate values.  It's crazy...

I try your suggestion below but got an error.  Can you see what I am doing wrong?.  Again, Thanks in advance.

IF OBJECT_ID('T1', 'u') IS NOT NULL
  DROP TABLE T1
GO
CREATE TABLE T1
(
   PKey             INT       NOT NULL PRIMARY KEY,
   drawerno         INT       NULL,
   DrawerStoreNo    INT       NULL,
   DrawerRegisterNo INT       NULL
)
GO

INSERT T1 VALUES (1, NULL, NULL, NULL ), (2, NULL, NULL, NULL ), (3, NULL, NULL, NULL )
GO

   SELECT *
     FROM T1
   GO

 -- Add primary key non-clustered.
   ALTER TABLE T1
   WITH NOCHECK ADD CONSTRAINT unci_T1 UNIQUE NONCLUSTERED ( drawerno, DrawerStoreNo, DrawerRegisterNo )
   GO

Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.T1' and the index name 'unci_T1'. The duplicate key value is (<NULL>, <NULL>, <NULL>).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.

quote:
Originally posted by visakh16

so you want to preserve existing duplicate values or clear the existing duplicate occurances as well?
if former make use of WITH NO CHECK option while creating constraint

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:40:37

WITH NO CHECK applies only to constraints. In above case you're creating a UNIQUE INDEX. So best thing would be remove the duplicates before hand.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources