How to identify last row on a insert statement

Question

Hello, I want to know which record is the last inserted on Detail Table B in the following order... for example If I have :

1 row Header Table A
--------------------
1 row Detail Table B
2 row Detail Table B
3 row Detail Table B
4 row Detail Table B (last)

I want to do some T-SQL or run a procedure if the 4 row is inserted... Thanks in advance!

Using SQL-Server 2000

Answer 1

SQL tables, by definition, have no implied ordering. Therefore you cannot reliably determine this based on the ordering of rows in your Detail table. You will need to add some sort of numeric column that contains the order in which you insert your rows.

Answer 2

From what I understand what you are asking, you are looking for triggers, WITH SOME CONDITIONS

Have a look at

• An Introduction to Triggers -- Part I
• Using Triggers In MS SQL Server
• Brief about Triggers in SQL Server 2000
• SQL Server Triggers
• Brief Introduction to Triggers in SQL Server 2000

Answer 3

Does that table have an identity column (E.g., "TableBId")? This is the numeric column Randy Minder talks about in his answer. If it does then it is pretty easy just use the ORDER BY function:

SELECT TOP 1 * FROM TableB ORDER BY Id DESC

If you do not have an identity column then you can add one by creating a new column (Id), setting it as the primary key and setting it's Identity specification on.

If you want to run some sql after a new item is inserted into a table you can use an insert trigger.

Can read about triggers here or look at astander's links below :)

Answer 4

Have a look at some help on triggers as advised above and then maybe look at the special Inserted and Deleted tables created and used by triggers.

Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

Every row that is inserted or deleted via a trigger is done via these tables i believe.

As you do your insert you could possibly query the insert table to find the last row that was pushed through it.

Answer 5

Using the IDENTITY to determine which record was inserted last could be very misleading. What if some records have been deleted, and you've reset your IDENTITY start value?

For example, you could potentially have records 1 - 10000, but delete from 1000-9000, and you decide to reset your identity to start again at 1000?

Triggers, on the other hand, could work but you need to weigh the consequences carefully. This could add a lot of load to your system.

How are you inserting the records? Through an application?

I will suggest looking at adding a timestamp column (or even just a regular DATETIME column with default value GETDATE()). This can aid in determining which record was inserted last in your transaction. For example:

-- begin your transaction 

-- insert statements here 

SELECT id
FROM TableB
WHERE timestampcol = (SELECT MAX(timestampcol) FROM TableB)

-- run your sproc based on the last record inserted

-- commit or rollback your transaction