Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to retrieve Table Variable data in a Trigger Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 6:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:35 AM
Points: 227, Visits: 706
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
Posted Tuesday, December 4, 2012 6:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 181, Visits: 639
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.
Post #1392412
Posted Tuesday, December 4, 2012 6:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:35 AM
Points: 227, Visits: 706
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
Posted Tuesday, December 4, 2012 6:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:42 AM
Points: 181, Visits: 639
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
Posted Tuesday, December 4, 2012 6:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:35 AM
Points: 227, Visits: 706
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
Posted Tuesday, December 4, 2012 6:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 12,889, Visits: 31,839
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 »

Add to briefcase

Permissions Expand / Collapse