being more specific

Hi all,

i want to write a trigger which returns a row to the application whenever a row is inserted into a table.

E.g.

When row R1 is inserted into a table T1.

the trigger should return the R1 to the application.

Wanna be coding monkey...

hi chaman,

its a bad practive for a trigger to be recordset returning.

trigger are best used for sending the result somewhere in the database

or for task such as maintaining the database consistency after an insert.

what are you going to do with it. can you specify what's your scenario so we can provide more help

if this is really what you need. you should write a stored procedure instead

regards,

joey

|||

Chaman:

I agree completely with Joey's position -- returning result sets from a trigger is in general a very bad idea. Also, there are other things that you need to consider should you continue with the result-set-from-a-trigger approach. It seems to me from your post that you have not considered impact whenever a single insert statement inserts multiple records into the table.

Are you motivated to return the row by any chance because you are using an identity column?

Dave

|||

Yes.. I also accept both Joeydj & Mugambo's comment..

If you want to read all about SQL Server Trigger visit this MSDN magazine Data Point article http://msdn.microsoft.com/msdnmag/issues/03/12/DataPoints/default.aspx#S4

http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/

if you don;t have time to read these article read the summary of these article on

http://weblogs.asp.net/akinney/archive/2003/11/14/37509.aspx

But really we don't know the purpose of Chaman's need...I will give the solution but before doing this read all these article and choose your decesion.( i am not ready to say its not possible at all in sql server )

on your trigger use the following statement

select * From Inserted (note: this statement only work on Trigger)

|||

Hi all,

thanks for your help,

well as i am new in this SQL let me be clear on my requirements..

1. we have a table T1, when a new row R is added in T1,

i have a service who triggers an action (say beep) depending on the entities in R,

i want a trigger who will send the data in R of T1 to that service.

Now, question 1 - is it possible to write sucha trigger?

2. if yes, how? how can a table return a value to a service.

Regards,

chaman.

|||How is this service listening for the prompts?|||

i think you need to create a service that listens or monitor the event in the db.

any way if your into service orinted design

it is not definetely the trigger that you will have to use.

you might as well consider "sql server notification services" and/or the service broker