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_UPDA TE]
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.compemploy0 1_c,i.comp employ02_c ,i.compemp loy03_c,i. compemploy 04_c,
i.compemploy05_c,i.compemp loy06_c,i. compemploy 07_c,i.com pemploy08_ c,
i.compemploy09_c,i.compemp loy10_c, i.compemploy11_c, i.compemploy12_c,
i.livingarr_c,i.livingarrc hange_d,i. livingarrl ast_c,i.la stsubstat_ d,i.create user_c, i.create_dt
FROM cd.client471 c
Inner join inserted i on c.clientid_c = i.clientid_c
--END---
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_UPDA
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.compemploy0
i.compemploy05_c,i.compemp
i.compemploy09_c,i.compemp
i.livingarr_c,i.livingarrc
FROM cd.client471 c
Inner join inserted i on c.clientid_c = i.clientid_c
--END---
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again thanks
ASKER