Interesting ERROR inINSERT Trigger

Question

Interesting ERROR inINSERT Trigger

Bhuvnesh

SSC Guru

Points: 59351
More actions
August 21, 2008 at 2:48 am

#103565

Following is the error
IF (SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = 'usp_dba') > 0 SELECT object_text FROM INSERTED ELSE RETURN
Msg 208, Level 16, State 1, Line 1
Invalid object name 'INSERTED'.
The statement has been terminated.
Here is the trigger code:
------------------------------------------------------------------------------
CREATE TRIGGER tr_SYNCUP_OBJECT_CONTROL_insert
ON SYNCUP_OBJECT_CONTROL
FOR INSERT
AS
DECLARE @sqlcmd NVARCHAR(MAX),@object_name NVARCHAR(MAX),@object_action NVARCHAR(MAX),@object_text NVARCHAR(MAX)
SELECT @object_action = object_action ,@object_name = object_name FROM INSERTED
IF (LEFT(@object_action,4) = 'DROP')
BEGIN
--SELECT @sqlcmd ='IF EXISTS (SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = ''' + @object_name + ''') BEGIN SELECT @object_text = object_text FROM INSERTED EXEC(@object_text) END ELSE RETURN '
SELECT @sqlcmd ='IF (SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = ''' + @object_name + ''') > 0 SELECT object_text FROM INSERTED ELSE RETURN '
print @sqlcmd
EXEC(@sqlcmd)
END
ELSE
BEGIN
SELECT @sqlcmd = object_text FROM INSERTED
print @sqlcmd
EXEC(@sqlcmd)
END
GO
------------------------------------------------------------------------------
Actually i want to execute SQL Command present in "SELECT object_text FROM INSERTED"
Please help me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bert-687390 SSC Eights! Points: 847 More actions · Answer 1

Hi,

It was a bit hard to look up the problem you are facing, but I assume this answers your question: The error occurs on 'line 1', and all that says in your case is that dynamic SQL is using the INSERTED virtual table in your code (that's the clue).

Okay, now the problem you are facing: The INSERTED table is specific for your context (imagine the problems rising if one INSERTED table would be shared between multiple contexts). EXEC() and sp_executesql use another context than the one you are working in. Therefore, the INSERTED table is not available in the EXEC() or sp_executesql scope.

HTH! Cheers!

[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]

Computers are made to solve problems you did not have in the days they didn't exist.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Bert's 100% right with the cause. inserted and deleted are only visible in the scope of the trigger, not any procedures called from it or dynamic SQL.

You have another problem with that trigger though (unrelated to the error). Triggers fire once per statement and contain as many rows in the inserted/deleted tables as were affected.

This line

SELECT @object_action = object_action ,@object_name = object_name FROM INSERTED

assumes that there is only one row in the inserted table. That may not be the case. What's going to happen if there are three rows in the inserted table?

Can you explain the purpose of the trigger? Maybe someone can suggest an alternative way of achieving what you're after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

prob SOLVED !!!!!!!!!!thanks for all

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bert-687390 SSC Eights! Points: 847 More actions · Answer 4

GilaMonster (9/18/2008)
This line
SELECT @object_action = object_action ,@object_name = object_name FROM INSERTED
assumes that there is only one row in the inserted table. That may not be the case. What's going to happen if there are three rows in the inserted table?

Good point, Gail. I did not check that part 😉 ...

[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]

Computers are made to solve problems you did not have in the days they didn't exist.