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?
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.)