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.

 

AcronyM_ID     NAME       Definition   Notes       acronym_uid

1                       xxx              xxxxx    xxxx          NULL

2                       xxx              xxxxx    xxxx          NULL

3                       xxx              xxxxx    xxxx          NULL

4                       xxx              xxxxx    xxxx          NULL

5                       xxx               xxxxx   xxxx          NULL

6                       xxx               xxxxx   xxxx          NULL

7                       xxx               xxxxx    xxxx         NULL

8                       xxx               xxxxx    xxxx        NULL

9                       xxx               xxxxx    xxxx         9

10                      xxx           xxxxx         xxxxx       10

thanks
gaurav sharmaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"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.

Duy PhamFreelance IT ConsultantCommented:
Try  this:

CREATE TRIGGER [dbo].[UpdateAcronym_UID] ON [dbo].[test] AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @LastInsertedAcronymID INT
    SELECT @LastInsertedAcronymID = IDENT_CURRENT('test')

    UPDATE [dbo].[test] SET Acronym_UID = @LastInsertedAcronymID WHERE Acronym_ID = @LastInsertedAcronymID  AND Acronym_UID IS NULL
END
GO

Open in new window

0
Anthony PerkinsCommented:
What happens if more than one row is added?
Also be careful with suing IDENT_CURRENT as the user will need to own the table and you may pick the wrong value.
0
Duy PhamFreelance IT ConsultantCommented:
@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:

INSERT [test](Name, Definition, Notes, Acronym_UID) VALUES(@Name, @Definition, @Notes, -1)

Open in new window


This is to keep the old existing rows in test table unchanged (leaving Acronym_UID IS NULL).

2. Then in the trigger we will update Acronym_UID for all the rows having Acronym_UID = -1

CREATE TRIGGER [dbo].[UpdateAcronym_UID] ON [dbo].[test] AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON

    UPDATE [dbo].[test] SET Acronym_UID = Acronym_ID WHERE Acronym_UID = -1
END
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Anthony PerkinsCommented:
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.
0
Duy PhamFreelance IT ConsultantCommented:
@Anthony:  Thanks so much for the tips.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
gaurav sharma, do you still need help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.