Using Trigger to get ID on Insert - SQL 2005

Question

I have a table (table_a) that, upon insert, needs to retrieve the next available id from the available_id field in another table (table_b) to use as the primary key in table_a, and then increment the available_id field in table_b by 1. While doing this via stored procedures is easy, I need to be able to have this occur on any insert into the table.

I know I need to use triggers, but I am unsure how to code this. Any advice?

Basically this is my dilema:
I need to ensure 2 different tables have unique id's throughout. What would be the best way to do this without using GUID's? (Some of this code cannot be controlled on our end and requires ints as id's).

Answer 1

My advice is DON'T! Use an identity field instead.

In the first place, inserts can have multiple records and so a trigger to properly do this would have to account for that making it rather tricky to write. It would have to be an instead of trigger which is also tricky as you wouldn't have one of the required values (I assume your ID field is required) in the initial insert. In the second place two inserts going on at the same time could try to pick the same number or could lock the second connection for a good bit of time if you are doing a large import of data in one connection.

Answer 2

You could use an Oracle-style sequence, described here, calling it either via a trigger or from your application (providing the resulting value to your insert routine):

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

He mentions these issues to consider:

• What if two processes attempt to add a row to the table at the exact same time? Can you ensure that the same value is not generated for both processes?

• There can be overhead querying the existing data each time you'd like to insert new data

• Unless this is implemented as a trigger, this means that all inserts to your data must always go through the same stored procedure that calculates these sequences. This means that bulk imports, or moving data from production to testing and so on, might not be possible or might be very inefficient.

• If it is implemented as a trigger, will it work for a set-based multi-row INSERT statement? If so, how efficient will it be? This function wouldn't work if called for each row in a single set-based INSERT -- each NextCustomerNumber() returned would be the same value.