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 calls to a stored procedure?

Hi folks,
One recurring problem we have is where to find calls to a stored
procedure when that sp may have changed and calls to it need to be
updated.
I know you can see the dependancies by right-clicking on the sp in
enterprise manager but we have found that this is not reliable and does
not pick up all occuranaces.
Does anybody know of a tool or method other than scripting the entire
schema creation.
Chris> Does anybody know of a tool or method other than scripting the entire
> schema creation.
Not that I am aware of any other work arounds. sysdepends is mostly not
dependable for sure. So you might need to script it out and compare.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
<chris-s@.mailcity.com> wrote in message
news:1109328369.430554.180420@.o13g2000cwo.googlegroups.com...
> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>|||Not sure about Enterprise Manager, but Query Analyzer does this and I've
found it to be reliable.
Press [F8] to bring up the object browser, drill into the stored procedure
and there's a folder for all parameters and dependencies.
However...I would have thought that this would use the same functionality
as EM, so should be equally (un)reliable.
Other alternative is to script the DB.
Trivial to do in EM, open up in Notepad and simply do a text search.
Of course...this only shows intra-database dependencies...if these SPs are
called by external applications then you've got to search through the data
access classes of all these [or refer to the associated documentation :-) ]
Griff|||have a look at DB Ghost (http://www.dbghost.com) for a complete approach to
database change management. You may also wish to read a white paper on
automated database change management -
http://www.innovartis.co.uk/pdf/ In...Mgt.
pdf
"chris-s@.mailcity.com" wrote:

> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>|||http://vyaskn.tripod.com/code/searc...cedure_code.txt
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<chris-s@.mailcity.com> wrote in message
news:1109328369.430554.180420@.o13g2000cwo.googlegroups.com...
> Hi folks,
> One recurring problem we have is where to find calls to a stored
> procedure when that sp may have changed and calls to it need to be
> updated.
> I know you can see the dependancies by right-clicking on the sp in
> enterprise manager but we have found that this is not reliable and does
> not pick up all occuranaces.
> Does anybody know of a tool or method other than scripting the entire
> schema creation.
> Chris
>