How do I retrieve the last inserted row?

Question

Post reply

How do I retrieve the last inserted row?

MeThinks

SSC Eights!

Points: 851
More actions
September 27, 2007 at 4:34 am

#178963

I'm using MSSQL 2005 and the scenario is like this:
1. Insert a single row into a table
2. Retrieve my last inserted row (mine, I don't care if meanwhile someone inserted another row into the same table)
Sound's simple, but I have problems with getting values back from tables with INSERT triggers.
I tried using the OUTPUT clause of the INSERT command, and what I came up with use something like this:
CREATE TABLE [dbo].[X_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[NAME] [varchar](30) NULL,
[CDATE] [datetime] NOT NULL
)
CREATE TRIGGER [dbo].[X_TEST_INSERT]
ON [dbo].[X_TEST]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE X SET X.CDATE=GETDATE()
FROM X_TEST X (NOLOCK), INSERTED I
WHERE X.ID=I.ID
END
DECLARE @X TABLE(
ID int NULL
,NAME varchar(30) NULL
,CDATE datetime NULL)
INSERT INTO X_TEST WITH (ROWLOCK) OUTPUT INSERTED.* INTO @X select
'My Name'
,'2007-01-01'
SELECT * FROM @X
The script is generated automatically by the client application who gets the table name and the values to be inserted.
At first, everything seemed to be OK, I got one line back, EXCEPT that the value for CDATE was '2007-01-01' (my inserted value), not the GETDATE() value at the time of INSERT.
So, my question is: how do I get the correct value for the columns affected by a trigger?
P.S. The IDENTITY column above is only for example. I already considered the scenario with each table having and IDENTITY column and getting back the row with IDENTITY_COLUMN = SCOPE_IDENTITY(). What I'm trying to do is to add a feature to an application (the users inserts a record and gets back the real values, without refreshing the data) and adding a column to each table would be a huge amount of work.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

It seems that you already have an Identity Column on your table?

What don't you use that and then retrieve the dat from there?

Also does this have to be done in a trigger?

Why don't you do it in your Insert Procedure after you run the insert statement?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

MeThinks SSC Eights! Points: 851 More actions · Answer 2

As I said in P.S., not all tables have an IDENTITY column.

And I don't have an Insert Procedure.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Unfortunately, the OUTPUT clause will always return the data from your action, not the trigger. If you have a primary key or something that uniquely identifies your records (which it is always good practice to have), and your trigger does not update these fields, simply join your OUTPUT data to the table to return the results.

CREATE TABLE [dbo].[X_TEST](

[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[NAME] [varchar](30) NULL,

[CDATE] [datetime] NOT NULL

)

GO

CREATE TRIGGER [dbo].[X_TEST_INSERT]

ON [dbo].[X_TEST]

FOR INSERT

AS

BEGIN

SET NOCOUNT ON;

UPDATE X SET X.CDATE=GETDATE()

FROM X_TEST X (NOLOCK), INSERTED I

WHERE X.ID=I.ID

END

GO

DECLARE @X TABLE(

ID int NULL

,NAME varchar(30) NULL

,CDATE datetime NULL)

INSERT INTO X_TEST WITH (ROWLOCK) OUTPUT INSERTED.* INTO @X select

'My Name'

,'2007-01-01'

SELECT T.* FROM @X X

INNER JOIN X_TEST T ON X.[ID] = T.[ID]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Adding an Identity column to each table isn't that much work if you write a little dynamic SQL to do it for you... especially if you have it all worked out as to how to use it to do the things you say.

And, if there's ever a chance that you'll insert more than one row into the table in a given Insert (like, during a batch process), writting a trigger to handle a single row will become "Death by SQL" sometime in the near future. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tim Riley-492074 SSCommitted Points: 1549 More actions · Answer 5

Not sure, but how about using the 'insert' table in SQL? I'm sure of one thing, I'll be corrected if I'm wrong.

Tim

What do you call a Local Area Network in Australia? A LAN down under.