CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
BJ Patel
BJ Patel

Reputation: 6268

How can I set the primary key value to other col in table when I insert a new row in SQL Server

I am using LINQ-to-SQL class. I am inserting a new row using LINQ method object.InsertOnSubmit().

I need to set same value which is generate by SQL Server (using Identity) for table primary key column.

Now I need the same value at the time of inserting new row into table. And set the same value for other column in the same table at the time of insert only.

As I cannot update as after inserting because table has UPDATE TRIGGER.

I tried the following 

_db.EmpNews.InsertOnSubmit(_EmpNews);
...
_db.DisplaySeq = _EmpNews.ID;
...
_db.SubmitChanges();

Where ID is the auto-generated (Identity) column.

Upvotes: 0

Views: 1678

Answers (1)

marc_s
marc_s

Reputation: 754258

The first question really is: why would you need to store the same value in two separate columns in the same table? What do you need this for? Doesn't seem to make a lot of sense to me....

Since the value of the IDENTITY column is only available once the row has actually been inserted, there is no way to get that value and set it to another column before the row has indeed been saved to the database table.

That basically leaves three options to get that value and store it somewhere else:

  • you can write an AFTER INSERT trigger that just set the other column to the value that's just been inserted in the IDENTITY column

  • you could wrap the whole saving process into a stored procedure which you call from your C# code (instead of just saving the object) and you would do the INSERT of the row, then get the newly created IDENTITY value and update the row again with that new value. But that would cause an UPDATE to happen - which you seem to say is impossible for you because of an UPDATE trigger (not quite clear on why this should be a problem....)

  • you can write two lines of C# code to get the IDENTITY value after it's been inserted (and available in the ID property of your object) and then store the object a second time. But that, too, would cause an UPDATE to happen - which you seem to say is impossible for you because of an UPDATE trigger (not quite clear on why this should be a problem....)

So I guess your best option would be an INSERT trigger to do this.

Try something like this:

CREATE TRIGGER trInsertEmpNews 
ON dbo.EmpNews AFTER INSERT 
AS BEGIN
    UPDATE dbo.EmpNews 
    SET DisplaySeq = i.ID
    FROM INSERTED i
    WHERE dbo.EmpNews.ID = i.ID
END

Upvotes: 5

Related Questions