SQL TRIGGER - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL TRIGGER

Author

Topic

JR83
Starting Member

31 Posts

Posted - 2014-08-07 : 01:37:46

HI There

Im trying to look for duplicates in a table field.. field name is alphanumericCol and table is a user defined table

This is my trigger:

ALTER TRIGGER [dbo].[DUPLICATES]
ON [dbo].[AMGR_User_Fields_Tbl]

FOR INSERT, UPDATE
AS
DECLARE @Alphanumericcol VARCHAR (750)

-- This trigger has been created to check that duplicate rows are not inserted into table.
-- Check if row exists
SELECT @Alphanumericcol
FROM Inserted i, AMGR_User_Fields_Tbl t
WHERE t.AlphaNumericCol = i.AlphaNumericCol
AND t.Client_Id = i.Client_Id
-- (@Alphanumericcol = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

The result i get is, if i input a duplicate number it fills in a null in the field so my question is how do i get it to tell me its duplicate and let me insert a new one

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-07 : 02:44:57

This would be a sufficient trigger

ALTER TRIGGER	dbo.DUPLICATES
ON		dbo.AMGR_User_Fields_Tbl
AFTER		INSERT,
		UPDATE
AS

SET NOCOUNT ON;

IF EXISTS(SELECT 1 FROM dbo.AMGR_User_Fields_Tbl GROUP BY AlphanumericCol, ClientID HAVING COUNT(*) >= 2)
	BEGIN
		RAISERROR('This row already exists in the table', 16, 1)
		ROLLBACK TRAN
	END;

However, you don't need a trigger for this. All you need is a UNIQUE CONSTRAINT over {AlphanumericCol, ClientID}.
I would probably make a unique clustered index over {AlphanumericCol, ClientID}.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

JR83
Starting Member

31 Posts

Posted - 2014-08-07 : 03:15:54

I added a unique constraint and it doesnt seem to work as i can input duplicate entries

JR83
Starting Member

31 Posts

Posted - 2014-08-07 : 04:50:53

how can i incorporate this into my trigger:

Assuming that is an Alphanumeric field you should be checking what value is in the alphanumeric column from inserted, comparing it with whatever is in the AMGR_User_Fields_Tbl to see if there’s a duplicate and then raising the error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources