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

问题:

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?

回答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.)

回答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 

回答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.

回答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.