How to retrieve Table Variable data in a Trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to retrieve Table Variable data in a...

How to retrieve Table Variable data in a Trigger

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Tuesday, December 04, 2012 6:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

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

Post #1392406

bugg

Posted Tuesday, December 04, 2012 6:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 123, Visits: 402

Minnu (12/4/2012)

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.

Post #1392412

Minnu

Posted Tuesday, December 04, 2012 6:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

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
----------------------
101 Paint
102 Walls
103 Furn
----------------------

Trigger:

Create trigger Trigger_Name
(
---
---
)

EXEC Stored_procedure @ID
select * from #temp_table

--

But it is not working

Syntax..Or query...
.Plz

Post #1392438

bugg

Posted Tuesday, December 04, 2012 6:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 123, Visits: 402

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

Post #1392445

Minnu

Posted Tuesday, December 04, 2012 6:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 122, Visits: 366

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

Post #1392448

Lowell

Posted Tuesday, December 04, 2012 6:54 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 11,638, Visits: 27,720

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1392453

« Prev Topic | Next Topic »

Permissions