How can I edit values of an INSERT in a trigger on SQL Server?

user_340760 0
Viewed 107274

I have the table Tb

ID | Name   | Desc
-------------------------
 1 | Sample | sample desc

I want to create a trigger on INSERT that will change the value of the inserting Desc, for example:

INSERT INTO Tb(Name, Desc) VALUES ('x', 'y')

Will result in

ID | Name   | Desc
-------------------------
 1 | Sample | sample desc
 2 | x      | Y edited

In the above example I got the value of the inserting Desc changed it to uppercase and added edited on the end.

That's what I need, get the Desc that is being inserted and modify it.

How can I do that?

Is it better to handle it after the insert with an update? Or make a trigger with INSTEAD OF INSERT and modify it everytime the table structure changes?

triggers sql-server-2008 tsql sql-server sql
6 Answers

Don't use IDENTITY, but use UNIQUEIDENTIFIER and NEWID():

CREATE TRIGGER MyTrigger ON MyTable INSTEAD OF INSERT AS BEGIN
; select * into #MyTriggertTempTable from inserted
; update #MyTriggertTempTable set ID=NEWID() where ID is null
; insert into MyTable select * from #MyTriggertTempTable
END
user_80538050

You can pseudo update a record after an INSERT using triggers, but be aware that you are actually UPDATING the record, not replacing the value before an INSERT.

CREATE TRIGGER [MISNT].[MyTriggerToReplaceColumnValueOnINSERT]
  ON [dbo].[myTableThatIsDoingTheINSERT]
  AFTER INSERT   --I prefer AFTER as it really is about after the fact!
AS
BEGIN
    UPDATE upd
    SET upd.ColA = 'some value'
    FROM [dbo].[myTableThatIsDoingTheINSERT] AS upd
        INNER JOIN inserted AS i ON i.ID = upd.ID
    WHERE upd.COLB IS NULL --optional WHERE if you want to only affect certain records
END
GO
user_21811880
Related