Avatar of John Mahoney
John Mahoney
 asked on

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

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---
Microsoft SQL ServerMicrosoft SQL Server 2005Windows Server 2003

Avatar of undefined
Last Comment
John Mahoney

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Mahoney

ASKER
Thank you.
John Mahoney

ASKER
Again thanks
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck