SYNTAX ERROR - 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

SYNTAX ERROR

Author

Topic

JR83
Starting Member

31 Posts

Posted - 2014-08-13 : 11:05:02

hi there

i keep getting the following error:

Incorrect syntax near the keyword 'END'.

here is my code:

ALTER TRIGGER [dbo].[DUPLICATES] ON TableWithDups
-- When inserting or updating
AFTER INSERT, UPDATE AS

-- Declare the variables
DECLARE @AN varchar(200)

-- Set the variable to the value that has been inserted.
SELECT @AN = col1 FROM inserted

-- If the AlphanumericCol is not null
IF @AN IS NOT NULL
BEGIN
-- If a duplicate has been found, rollback trasaction and report error.
if exists ( select * from TableWithDups t
inner join inserted i on i.col1=t.col1)
BEGIN
RAISERROR ('Serial Number: %s already exists in the database',16,1, @AN)
ROLLBACK TRANSACTION
END
END
--- If not exists insert into table
BEGIN
if not exists (SELECT i.col1 FROM inserted AS i
JOIN (SELECT COL1 FROM TableWithDups/*table with trigger*/) AS w
ON w.col1 = i.col1
--GROUP BY i.col1

END

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-13 : 11:27:11

This is wrong on many levels. Your trigger checks the rows AFTER they are inserted and thus your EXISTS-join will always be true.
And you are also working with the assumption that there will only be one row inserted in any given time.

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

SET NOCOUNT ON;

DECLARE @AN NVARCHAR(MAX);

SET	@AN =	STUFF(	(
				SELECT	N', ' + CAST(Col1 AS NVARCHAR(100))
				FROM	(
						SELECT		t.Col1
						FROM		dbo.TableWithDups AS t
						INNER JOIN	inserted AS i ON i.Col1 = t.Col1
						GROUP BY	t.Col1
						HAVING		COUNT(*) >= 2
					) AS d
				FOR XML	PATH(''),
					TYPE
			).value('(.)', 'NVARCHAR(MAX)'), 1, 2, N'');

IF @AN > N''
	BEGIN
		RAISERROR ('Serial Number(s): %s already exists in the database', 16, 1, @AN)
		ROLLBACK TRANSACTION
	END

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

JR83
Starting Member

31 Posts

Posted - 2014-08-14 : 03:40:41

Thanks alot. yours makes more sense..

Just a quick question, if i have more than one Col1, with the same name, how do i get my code to reference the checks only on the col1 that i want? does this make sense?

JR83
Starting Member

31 Posts

Posted - 2014-08-14 : 04:09:29

for example:

if i have two alphanumeric columns... same name.. different columns..
how do i only reference one..

JR83
Starting Member

31 Posts

Posted - 2014-08-14 : 04:52:37

A more detailed approach

My duplicate check runs against the entire Col1 column. There might be another Col1 field that has the same value, and that might be valid, so you need to ensure that when you do the check, you are checking only against the serial number field and not all Col1 fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources