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?
Using SQL Server Management Studio Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to modify. Expand Triggers, right-click the trigger to modify, and then click Modify. Modify the trigger, and then click Execute.
Introduction to SQL Server CREATE TRIGGER statementThe CREATE TRIGGER statement allows you to create a new trigger that is fired automatically whenever an event such as INSERT , DELETE , or UPDATE occurs against a table. In this syntax: The schema_name is the name of the schema to which the new trigger belongs.
In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER UPDATE clause to specify the time to invoke the trigger. Third, specify the name of the table to which the trigger belongs after the ON keyword.
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.)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With