How to find out row that was just inserted?

hi folks,
Is it possible to find out the row which was just inserted in a table? I am creating a trigger and i m trying to access the row which was just inserted into the table. Any suggestions? Thanks a million!
GayathriWithin a trigger you can access the newly inserted/updated/deleted fields by referencing virtual tables named:
"inserted" - holds new and updated records
"deleted" - holds deleted records

You just reference these tables as you would any permanent table:

select count(*)
from YOURTABLE
inner join INSERTED
on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY

blindman|||My code is as follows

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = Inserted.JobNumber
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

But i get an error saying
"The column prefix 'Inserted' does not match with a table name or alias name used in the query. "

Is there any other syntax that i am missing?|||try this...

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = (select Inserted.JobNumber from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||Did modify to the code below but again it says "Invalid Object Name 'Inserted':(

Originally posted by rokslide
try this...

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = (select Inserted.JobNumber from inserted)
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||this is the suggestion from MS...

CREATE TRIGGER Trigger1 ON dbo.TableA
FOR INSERT
AS
DECLARE @.var1 INT
DECLARE @.VarA int

SELECT @.VarA = SELECT col2 FROM inserted
DECLARE cursor1 CURSOR FOR
SELECT col1
FROM TableB
WHERE col1 = @.VarA

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @.var1

CLOSE cursor1
DEALLOCATE cursor1
GO|||So modifying it for your purposes...

DECLARE @.SearchJobNumber int
DECLARE @.job_number int
SELECT @.JobNumber = SELECT JobNumber from inserted

DECLARE job_number_cursor CURSOR FOR
SELECT JobNumber
FROM asiapac702_test.dbo.tblCustServiceHistoryHdr
where JobNumber = @.JobNumber
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor|||Here is how to rewrite your original statement to avoid the error you got:

DECLARE job_number_cursor CURSOR FOR SELECT JobNumber FROM Inserted
OPEN job_number_cursor
FETCH NEXT FROM job_number_cursor INTO
@.job_number

CLOSE job_number_cursor
DEALLOCATE job_number_cursor

The big question is why you are putting a cursor in a trigger. This is the database equivalent of pouring sewage into your gas tank.

blindman|||Cursor in trigger? Do not think it is good idea...|||I got the impression that things are getting on the wrong path. Summa summarum, the answer for your original question is given by Blindman in his first post:

select count(*)
from YOURTABLE
inner join INSERTED
on YOURTABLE.PRIMARYKEY = INSERTED.PRIMARYKEY

But this should be used in the trigger written on the table which you are interested in. Got the picture?
You probably get the error "Invalid Object Name 'Inserted'" because you are not using the code in the wright place.

Best regards!|||Can you not just use the @.@.IDENTITY property?

Or does this not work in a trigger?

How to find an inserted value in a table

Hello,

I have 2 tables, and use objectdatasource and stored procedures, with sql server.

Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table.

How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one.

Thanks for your help.

In your stored proc use @.@.IDENTITY to get the ID for the newly inserted row.

SET NOCOUNT ONINSERT INTO...SELECT @.@.IDENTITY as IDCustomer

Now in your code execute the proc as if it were a SELECT one and you'll get a single-row result set back. Or if using the SqlCommand object use ExecuteScalar.

|||

Perfect!

Thanks for the help.

|||Please use SCOPE_IDENTITY() rather than @.@.IDENTITY to retrieve the ID of the last inserted row. Check out books on line to understand why.