How to retrieve Table Variable data in a Trigger

Question

How to retrieve Table Variable data in a Trigger

Minnu

SSCertifiable

Points: 6451
More actions
December 4, 2012 at 6:01 am

#267690

Hi SSC Team,
i have created a stored procedure with a "table variable" in it,
inserted some records into table variable based on some conditions.
and my requirement is :
i have another trigger, i want to use the data available in table variable
How it is possible.
Please Help

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

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

bugg SSCertifiable Points: 7567 More actions · Answer 1

Minnu (12/4/2012)
Hi SSC Team,
i have created a stored procedure with a "table variable" in it,
inserted some records into table variable based on some conditions.
and my requirement is :
i have another trigger, i want to use the data available in table variable
How it is possible.
Please Help

A table @variable will only hold is value for the session its in.

Rather create a #temp table which you can use in your other 'trigger' then drop it after you have finished with it.

Minnu SSCertifiable Points: 6451 More actions · Answer 2

using a stored procedure inserted records in to a #Temp_Table.

how to retieve the data in a Trigger

am using below

#Temp_Table

----------------------

Id | Name

----------------------

101Paint

102Walls

103Furn

----------------------

Trigger:

Create trigger Trigger_Name

(

---

)

EXEC Stored_procedure @ID

select * from #temp_table

--

But it is not working

Syntax..Or query...

.Plz

bugg SSCertifiable Points: 7567 More actions · Answer 3

Don't think you can create a trigger on a temp table:

see this post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96386

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Sorry for asking,

my requirement is:

i've to call the stored procedure based on "id" and get the corresponding Name from the temp table.

Please suggest

Lowell SSC Guru Points: 323518 More actions · Answer 5

Minnu (12/4/2012)
Sorry for asking,
my requirement is:
i've to call the stored procedure based on "id" and get the corresponding Name from the temp table.
Please suggest

wouldn't that be a simple select?

SELECT name from #temp where ID = @param

am i misreading the quesiton, or is there more to it than that?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!