如何在 SQL Server 上的触发器中编辑 INSERT 的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3580123/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I edit values of an INSERT in a trigger on SQL Server?
提问by BrunoLM
I have the table Tb
我有桌子 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 上创建一个触发器来改变 inserting 的值Desc，例如：
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 Descchanged it to uppercase and added editedon the end.
在上面的示例中，我将插入的值Desc更改为大写并添加edited到末尾。
That's what I need, get the Descthat is being inserted and modify it.
这就是我需要的，获取Desc正在插入的内容并进行修改。
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?
插入后用更新处理它会更好吗？还是用 INSTEAD OF INSERT 做一个触发器，并在每次表结构改变时修改它？
回答by Shannon Severance
Use an after insert trigger. Join from the insertedpseudo table to Tbon the primary key. Then update the values of desc. Something like: (But may not compile)
使用插入后触发器。从inserted伪表连接到Tb主键。然后更新 desc 的值。类似的东西：（但可能无法编译）
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 insertstatement 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.
这个触发器发生在插入发生之后，但在insert语句完成之前。因此，新的、不正确的值已放置在目标表中。这个触发器不需要随着列的添加、删除等而改变。
CaveatIntegrity 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.)
警告完整性约束在 after 触发器触发之前强制执行。所以你不能施加检查约束来强制执行正确的 DESC 形式。因为这会导致语句在触发器有机会修复任何东西之前失败。（在依赖它之前请仔细检查这一段。我已经有一段时间没有写触发器了。）
回答by JonVD
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.
我不确定您将在哪里获得 desc 的实际新值，但我假设您是从另一个表或其他表中获取的。但是你可能有理由想要这样做，所以下面是我将如何去做的一个例子。
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.
你想要的是一个 INSTEAD OF INSERT 触发器，它用你给它的每一个逻辑触发而不是在表上插入。
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)
哦还要确保将 [] 放在 Desc 周围，因为它是 sql server 中的关键字（代表降序）
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.
如果您想让它更健壮一点，以便它不依赖于表结构，您可以使用 AFTER INSERT 触发器来像这样更新该字段。
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
回答by Vadzim
Temp table can help to use INSTEAD OF INSERTtrigger while avoiding to explicitly listing all unrelated table columns:
临时表可以帮助使用INSTEAD OF INSERT触发器，同时避免显式列出所有不相关的表列：
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.
另请注意，SQL Server 触发器为批量 DML 操作触发一次，并且必须正确处理多行插入。
回答by bobs
You may want to look at INSTEAD OFtriggers.
您可能想查看INSTEAD OF触发器。
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.
这将允许您在数据进入表之前对其进行操作。触发器负责将数据插入到表中。
