INVALID OBJECT NAME 'inserted' on insert trigger

[Elvis]

Hi,
i have created a trigger for insert with a dynamic query and i have
this error:
"INVALID OBJECT NAME 'inserted'".

The code i used on my insert trigger is this:

CREATE TRIGGER ADM.INSERT_ ON TMU_2
FOR INSERT
AS
DECLARE @TYPE AS VARCHAR(5)
DECLARE @DOCUMENTCODE AS VARCHAR(4)
DECLARE @REVNUMBER AS VARCHAR(4)
DECLARE @ORIGINAL AS VARCHAR(4)
SET @TYPE = 'TMU_2'
SET @DOCUMENTCODE = 'UF30'
SET @REVNUMBER = 'UF33'
SET @ORIGINAL = 'UF39'
DECLARE @DOCVALIDI INTEGER
DECLARE @QRY AS NVARCHAR(4000)
SET @QRY = 'SELECT @DOCVALIDI = COUNT(*) FROM ADM.'+@TYPE+' AS OLD,
inserted AS NEW WHERE ( OLD.'+@DOCUMENTCODE+'=NEW.'+@DOCUMENTCODE+'
AND OLD.'+@REVNUMBER+'=NEW.'+@REVNUMBER+' AND OLD.'+@ORIGINAL+'=''S'')
'
exec sp_executesql @QRY, N'@DOCVALIDI int output', @DOCVALIDI output
IF (@DOCVALIDI > 1) /* I need to make this control with dynamic
query */
BEGIN
RAISERROR ('DOCUMENT ALREADY EXISTS !',11,1)
ROLLBACK TRANSACTION
RETURN
END

Someone can tell me how can i modify the SET @QRY ?

Thanks,
Elvis

[Wayne Snyder]

Although I haven't tested this I suspect the problem is that sp_executesql
is a separate batch. That means it doesn't run inside the trigger, which
means it can not see the inserted or deleted table...

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

"Elvis" <elv...@libero.it> wrote in message
news:26712d06.03102...@posting.google.com...

[Anthony Faull]

Try using dynamic sql only to create the trigger.

CREATE PROCEDURE dbo.Create_Trigger_For_Insert
@TYPE varchar(5),
@DOCUMENTCODE varchar(4),
@REVNUMBER varchar(4),
@ORIGINAL varchar(4)
AS
EXEC('CREATE TRIGGER ADM.INSERT_' + @TYPE + ' ON ADM.' + @TYPE + '
FOR INSERT
AS
IF EXISTS
(
SELECT 1
FROM ADM.' + @TYPE + ' AS OLD
INNER JOIN inserted AS NEW
ON OLD.' + @DOCUMENTCODE + ' = NEW.' + @DOCUMENTCODE + '
AND OLD.' + @REVNUMBER + ' = NEW.' + @REVNUMBER + '
AND OLD.' + @ORIGINAL + ' = ''S''
)

BEGIN
RAISERROR (''DOCUMENT ALREADY EXISTS !'',11,1)
ROLLBACK TRANSACTION
RETURN

END')
GO

EXEC dbo.Generate_Trigger_For_Insert
@TYPE = 'TMU_2',
@DOCUMENTCODE = 'UF30',
@REVNUMBER = 'UF33',
@ORIGINAL = 'UF39'

"Wayne Snyder" <wsn...@computeredservices.com> wrote in message
news:O0lWWHUn...@tk2msftngp13.phx.gbl...