?
Solved

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

Posted on 2016-10-19
4
Medium Priority
?
85 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---
0
Comment
Question by:John Mahoney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1000 total points
ID: 41850865
You don't need to worry about that column.  EG: you add the identity column and then you do your insert as normal (ignoring the identity column).  When you do the next select it will auto increment for you.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 41852478
For a column with the IDENTITY property, SQL Server automatically generates the value for the column during an INSERT.  Therefore, you can't provide the value yourself (exception: you can explicitly "tell" SQL that you will be providing the identity values to be INSERTed).

For example:
CREATE TABLE table1 (column1 int IDENTITY(1, 1) NOT NULL, column2 varchar(30))
INSERT INTO table1 VALUES('column2_value')
Note that you basically ignore the identity column on a normal INSERT statement.
0
 

Author Comment

by:John Mahoney
ID: 41852484
Thank you.
0
 

Author Closing Comment

by:John Mahoney
ID: 41852488
Again thanks
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question