Interesting ERROR inINSERT Trigger

Question

Interesting ERROR inINSERT Trigger

Viewing 4 posts - 1 through 5 (of 5 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: 1004514 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.