Need Help in SQL script

Table(test) has 4 columns AcronyM_ID, NAME, Definition, Notes with some data in it.And AcronyM_ID is identity column. Now I have to add a new column called acronm_UID.

AcronyM_ID NAME Definition Notes

1 xxx xxxxx xxxx

2 xxx xxxxx xxxx

3 xxx xxxxx xxxx

4 xxx xxxxx xxxx

5 xxx xxxxx xxxx

6 xxx xxxxx xxxx

7 xxx xxxxx xxxx

8 xxx xxxxx xxxx

need to add a new column in table (test) called Acronym_UID. Note that this is NOT an identity column like Acronym_ID. Need to Create a TRIGGER that will copy value of Acronym_ID to Acronym_UID ONLY upon creation of new Acronym. If the test table is copied/migrated to different DB or server, the Acronym_UID should not be overridden with the new value of Acronym_ID.

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

@Anthony: You're right that above trigger only aims at handling single row added, and of course so does wrong value of IDENT_CURRENT come in. But could you elaborate more about 'the need to own the table' issue? I assume that either the user who creates the trigger or the user adds new row should be the owner of the table, aren't them?

@gaurav: To handle the case of multiple rows are added at the same time, you might try below option:

1. When inserting new rows into test table, set Acronym_UID to a hard-coded number (such as -1) to indicate newly added rows. For example:

I assume that either the user who creates the trigger or the user adds new row should be the owner of the table, aren't them?
No. The user should never own anything. The principle that you should always keep in mind is that you should always set the minimum permissions necessary to accomplish the job.

To handle the case of multiple rows are added at the same time, you might try below option:
This is best handled with the INSERTED logical table.