What is an elegant way to make two fields auto increment?

Question

In SQL Server, I have two fields, one is primary and has an identity (AN_ID) then I have another one that for legacy reasons (don't ask) has to be updated reflecting the AN_ID. Currently they have the exact same value. I was wondering if there are pure T-SQL query (no stored procedures) to make it auto incremente.

Thank you very much

Edit: I'm forced to SQL Server 2000

Answer 1

If both fields can contain the same value, then you can use an insert trigger or a computed column (new in SQL 2005) to accomplish this.

EDIT: Something like this should work.

CREATE TRIGGER MyTable_AI ON MyTable
AFTER INSERT AS
    UPDATE MyTable
    SET OTHER_ID = AN_ID
    WHERE AN_ID IN (SELECT AN_ID FROM inserted);

Answer 2

The second column could be a computed column with the formula AN_ID.

You could also create a view with an alias of AN_ID.

Answer 3

The "right" solution is almost certainly to use a trigger, however if that falls under the heading of "no stored procedures" then you can fudge things a little by running two consective queries (which you should be able to send as one query from your calling app - be useful to know what environment you're using on top of SQL Server).

e.g. (very roughly)

INSERT INTO tbl (col1, col2, col3) values (@col1param, @col2param, @col3param);
UPDATE tbl SET not_an_id = an_id WHERE an_id = SCOPE_IDENTITY()