如何更新触发器中的插入字段 [英] How to update inserted field in trigger

问题描述

好吧，我的情况是这样的：

Ok, my situation is like this:

我有一个名为Company的表，我想在此表中添加的每条记录中添加一个触发器来检查如果列的名称不以'LTD'结尾，则在名称的末尾添加'LTD'。

I have a table named Company, and I want to add a trigger after every record I add in this table that checks if the column Name does not end with 'LTD' then add 'LTD' at the end of the Name.

我收到一条错误消息，说在'）'附近语法不正确。我该怎么做？

I get an error saying Incorrect syntax near ')'. How would I do this?

Create Trigger [Add_LTD] on Company
After Insert As
Update Company
Set Name = Name + ' LTD'
If Exists (Select Name 
From Inserted
Where Name Not Like '% LTD')  

推荐答案

您将需要以下内容：

CREATE TRIGGER [Add_LTD] on dbo.Company
AFTER INSERT AS
   UPDATE dbo.Company
   SET Name = Name + ' LTD'
   FROM Inserted i
   WHERE dbo.Company.CompanyID = i.CompanyID
     AND Name NOT LIKE '% LTD'

您需要将插入中的行连接到基础表中（以便仅更新那些新插入的行），并且最好的方法是使用主键（例如 CompanyID 之类的东西）来实现这一目标。

You need to join the rows in Inserted to your underlying table (in order to update just those rows that have been newly inserted), and the best way to do this is to use your primary key (something like a CompanyID) to achieve this.

这篇关于如何更新触发器中的插入字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！