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?
 
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.