CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
skmasq
skmasq

Reputation: 4511

Can SQL server instead of update force insert a new row

Is it possible in SQL Server 2008+ to force an UPDATE statement on table to be transformed into INSERT statement, thus creating new row with the old and updated columns?

Upvotes: 1

Views: 1363

Answers (1)

M.Ali
M.Ali

Reputation: 69524

Yes this is a typical scenario for an INSTEAD OF UPDATE TRIGGER.

Create the following trigger on your table and it will insert a row for each update made on your table. you can have a bit more logic inside your trigger but this is just a basic definition just to give you some idea.

Inside your INSTEAD of UPDATE trigger you will have access to two system tables Inserted and deleted.

Inserted table will hold new values for the row that was being updated by the Update statement.

Deleted table will hold Old values for the row that was being updated by the Update statement.

Demo Trigger

CREATE TRIGGER tr_Table_Instead_Of_Update
ON TABLE_NAME
INSTEAD OF UPDATE
AS
BEGIN
 SET NOCOUNT ON;

 INSERT INTO TABLE_NAME(Col1, Col2 , Col3)
 SELECT Col1, Col2 , Col3
 FROM inserted 

END

Upvotes: 3

Related Questions