Getting a syntax error from trigger

[murphyhg]

Hello, I am getting this syntax error from trigger and I cannot find it.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(0 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'd'.

USE [gsigmycfsite]
GO
/****** Object: Trigger [dbo].[ARTICLEMATRIX_ChangeTracking] Script Date: 03/09/2009 16:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[ARTICLEMATRIX_ChangeTracking] on [dbo].[ARTICLEMATRIX] for insert, update, delete

as

declare @AuditType char(1), @PKCols NVARCHAR(MAX), @SQL NVARCHAR(MAX)

--Find the Primary keys to be used in the inserted and deleted outer join

select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'ARTICLEMATRIX'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select * into #ins from inserted

select * into #del from deleted

if exists (select * from inserted)

if exists (select * from deleted)

SET @AuditType = 'U'

else

SET @AuditType = 'I'

else

SET @AuditType = 'D'

EXEC('INSERT INTO ARTICLEMATRIX_AUDIT (ID, ART_ID, ART_TYPES, ART_NAME, TIER, TIER_ID, ButtonLoc, quicklinks, highlight, LocName, REGIONALTAG, COUNTRYTAG, oldID, oldART_ID, oldART_TYPES, oldART_NAME, oldTIER, oldTIER_ID, oldButtonLoc, oldquicklinks, oldhighlight, oldLocName, oldREGIONALTAG, oldCOUNTRYTAG, AuditType)

SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d' + @PKCols )

 

[SQLBill]

Try this:

Change this:
EXEC('INSERT INTO ARTICLEMATRIX_AUDIT (ID, ART_ID, ART_TYPES, ART_NAME, TIER, TIER_ID, ButtonLoc, quicklinks, highlight, LocName, REGIONALTAG, COUNTRYTAG, oldID, oldART_ID, oldART_TYPES, oldART_NAME, oldTIER, oldTIER_ID, oldButtonLoc, oldquicklinks, oldhighlight, oldLocName, oldREGIONALTAG, oldCOUNTRYTAG, AuditType)

SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d' + @PKCols )


TO

DECLARE @testSQL VARCHAR(1000)
SET @testSQL = 'INSERT INTO ARTICLEMATRIX_AUDIT (ID, ART_ID, ART_TYPES, ART_NAME, TIER, TIER_ID, ButtonLoc, quicklinks, highlight, LocName, REGIONALTAG, COUNTRYTAG, oldID, oldART_ID, oldART_TYPES, oldART_NAME, oldTIER, oldTIER_ID, oldButtonLoc, oldquicklinks, oldhighlight, oldLocName, oldREGIONALTAG, oldCOUNTRYTAG, AuditType)
SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d' + @PKCols

PRINT @testSQL


The PRINT should show you what the script actually looks like just prior to it running.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875