We help IT Professionals succeed at work.

Inserting a column in a table that creates an ID and row number

189 Views
Last Modified: 2016-10-20
I have a trigger on a table (like an audit trigger) that updates a backup table whenever the main table is updated. If someone updates the main table "Client", all the changes are captured along with the old records in the "ClientBackup" table.
I created the backup table using INSERT INTO

What I want to do is add a column to this backup table for identification purposes, possibly to create a unique key (but not certain I need that yet), but I do want to add the column with row numbers to the backup table and auto increment that when new data is added.

Because this is updated via an UPDATE Trigger the column would need to auto update whenever the trigger fires.

I know I can add a column to an existing table in order to number the rows with

ALTER TABLE CLIENT ADD id INT IDENTITY(1,1)

The current table has 471 rows if I run the above code and add the IDENTITY column I'll have rows 1 through 471. How would I create a column that auto updates the next time the trigger fires? I'm learning SQL but in other code I would create a variable that holds the value of IDENTITY and then add one to the current value, but can I do that inside a trigger and in SQL?

Here is the trigger code
---- CODE----

CREATE TRIGGER [cd].[trg_MOTS_CLIENT_UPDATE]
ON [cd].[client471]
AFTER UPDATE
AS
SET NOCOUNT ON;
BEGIN
INSERT INTO cd.client471bckup
    ([uniqueid_c], [clientid_c],
    [txstatus_c], [compemploy01_c], [compemploy02_c], [compemploy03_c],
      [compemploy04_c], [compemploy05_c], [compemploy06_c], [compemploy07_c], [compemploy08_c],
    [compemploy09_c], [compemploy10_c], [compemploy11_c], [compemploy12_c],
    [livingarr_c], [livingarrchange_d], [livingarrlast_c], [lastsubstat_d], [createuser_c], [create_dt]
     )
SELECT
    i.uniqueid_c, i.clientid_c, i.txstatus_c,i.compemploy01_c,i.compemploy02_c,i.compemploy03_c,i.compemploy04_c,
    i.compemploy05_c,i.compemploy06_c,i.compemploy07_c,i.compemploy08_c,
    i.compemploy09_c,i.compemploy10_c, i.compemploy11_c, i.compemploy12_c,
    i.livingarr_c,i.livingarrchange_d,i.livingarrlast_c,i.lastsubstat_d,i.createuser_c, i.create_dt
FROM cd.client471 c
Inner join inserted i on c.clientid_c = i.clientid_c

--END---
Comment
Watch Question

Senior .Net Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you.

Author

Commented:
Again thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.