How can I edit values of an INSERT in a trigger on SQL Server?
Solution 1
Use an after insert trigger. Join from the inserted
pseudo table to Tb
on the primary key. Then update the values of desc. Something like: (But may not compile)
CREATE TRIGGER TbFixTb_Trg
ON Tb
AFTER INSERT
AS
BEGIN
UPDATE Tb
SET DESC = SomeTransformationOf(i.DESC)
FROM Tb
INNER JOIN inserted i on i.Id = Tb.Id
END
GO
This trigger happens after the insert has happened, but before insert
statement completes. So the new, incorrect values are already placed in the target table. This trigger will not need to change as columns are added, deleted, etc.
Caveat Integrity constraints are enforced before the after trigger fires. So you can't put on a check constraint to enforce the proper form of DESC. Because that would cause the statement to fail prior to the trigger having a chance to fix anything. (Please double check this paragraph before relying on it. It's been awhile since I've written a trigger.)
Solution 2
I'm not sure where your going to get the actual new value for desc, but I assume you are getting it from another table or some such. But you probably have a reason for wanting to do it this way so below is an example of how I would go about it.
What you want is called an INSTEAD OF INSERT trigger, it fire instead of an insert on the table with what every logic you give it.
CREATE TRIGGER trgUpdateDesc
ON Tb
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Tb (Name, [Desc])
SELECT Name, [Desc] + 'edited'
FROM inserted
END
GO
I've hard coded the word 'edited' in there as I'm not sure where you want to get the value, but you can easily replace that with a variable or a value from another table.
Oh also be sure the put the [] around Desc, as it is a key word in sql server (stands for descending)
Hope that helps!
Edit:
If you want to make it a little more robust so that it doesn't depend on the table structure as much you could use an AFTER INSERT trigger to just updates that field like so.
CREATE TRIGGER [dbo].[trgUpdateDesc]
ON [dbo].[Tb]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE Tb
SET [Desc] = UPPER(inserted.[Desc]) + ' Edited'
FROM inserted INNER JOIN Tb On inserted.id = Tb.id
END
Solution 3
Temp table can help to use INSTEAD OF INSERT
trigger while avoiding to explicitly listing all unrelated table columns:
CREATE TRIGGER trgUpdateDesc
ON Tb
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
select * into #tmp from inserted;
UPDATE #tmp SET Desc = Desc + 'edited' --where ...;
insert into Tb select * from #tmp;
drop table #tmp;
END
This code will not need to be fixed when new columns are added to the table.
But beware of some additional overhead of temp tables.
Also note that SQL Server triggers fire once for bulk DML operations and must correctly handle multi-row inserts.
Solution 4
You may want to look at INSTEAD OF
triggers.
CREATE TRIGGER Tb_InsteadTrigger on Tb
INSTEAD OF INSERT
AS
BEGIN
...
This will allow you to manipulate the data before it goes into the table. The trigger is responsible for inserting the data to the table.
BrunoLM
I'm a Developer for Fun! Things I like Code Play games Anime / Manga Contact information [email protected] LinkedIn Facebook Site - https://brunolm.github.io/ Blog - http://blog.codingwise.com/
Updated on July 09, 2022Comments
-
BrunoLM almost 2 years
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 addededited
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?
-
Claudio Redi over 13 yearsCan't you do this in a different way? I personally hate when I see weird things happening on the data and I finally realize that a ghost trigger was doing some stuff
-
BrunoLM over 13 yearsI will use this to fix a problem that comes from a code I don't have access. It inserts the
url
of an item, but it doesn't remove accents and special characters, I will make a trigger do that for me. -
Jim almost 12 yearsThis isn't such a hard concept and it boggles my mind that Microsoft can't provide such simple functionality like in Oracle where you can modify any values before insert by editing the :NEW values. i.e. if I want to always cast usernames to upper before inserting them (assuming I can't just do it in code for some reason), you could have a before insert trigger that does :NEW.username := UPPER(:NEW.username). In SQL Server though, you can't do the equivalent with values in the INSERTED temp table, and that really sucks.
-
Triynko almost 5 yearsYeah, you can. In the instead of trigger, just run an insert statement that selects all the columns from the inserted table into the real table, but modify the select for the column you want to make upper. For example, in the instead of insert trigger, just run a statement like
insert into Table (a,b,c) select a, ToUpper(b), c from inserted
. Microsoft tech blows oracle out of the water.
-
-
BrunoLM over 13 yearsI want to get the value of the inserted
Desc
and modify it. I should have said that. I will update my question, sorry for that -
BrunoLM over 13 yearsWhat if my table changes? I will have to update the trigger everytime? Isn't there another way to do that?
-
Lukas Eder over 8 yearsAlso, this solution won't make the actually inserted values available to the
OUTPUT
clause! -
CodenameCain about 8 yearsWhat is "Trgt" in this context?
-
Shannon Severance about 8 years@CodenameCain A mistake. Should be
Tb
or the from clause needs to befrom Tb Trgt
. thanks for catching that. -
Anders Lindén about 7 yearsThis produces the following error: An explicit value for the identity column in table 'Tb' can only be specified when a column list is used and IDENTITY_INSERT is ON.
-
Vadzim about 7 years@Anders, I guess, this approach may be incompatible with IDENTITY columns. But you may also try to drop this column in
#tmp
table inside trigger. -
Fandango68 over 2 yearsBut be careful because INSTEAD assumes you're in charge of all the columns in the table.
-
Fandango68 over 2 yearsThe OP was about updating a column value after the insert is triggered. Please elaborate your answer because it looks like it has nothing to do with the question asked.