SQL Server 2008 - 帮助编写简单的 INSERT 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1355921/
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
SQL Server 2008 - Help writing simple INSERT Trigger
提问by Anonymous Coward
This is with Microsoft SQL Server 2008.
这是 Microsoft SQL Server 2008。
I've got 2 tables, Employee and EmployeeResult and I'm trying to write a simple INSERT trigger on EmployeeResult that does this - each time an INSERT is done into EmployeeResult such as:
我有 2 个表，Employee 和 EmployeeResult，我正在尝试在 EmployeeResult 上编写一个简单的 INSERT 触发器来执行此操作 - 每次对 EmployeeResult 执行 INSERT 时，例如：
(Hyman, 200, Sales) (Jane, 300, Marketing) (John, 400, Engineering)
(Hyman, 200, 销售) (Jane, 300, 营销) (John, 400, 工程)
It should look up for the Name, Department entry pairs, such as
它应该查找 Name、Department 条目对，例如
(Hyman, Sales), (Jane, Marketing), (John, Engineering)
（Hyman，销售），（Jane，营销），（John，工程）
within the Employee table, and if such an employee does not exist, should insert that into the Employee table.
在 Employee 表中，如果这样的员工不存在，则应将其插入到 Employee 表中。
What I have is this with unknowns on how to fix the "???"s:
我所拥有的是关于如何修复“???”的未知数：
CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
IF EXISTS (SELECT COUNT(*) FROM Employee WHERE ???)
BEGIN
INSERT INTO [Employee] (Name, Department) VALUES (???, ???)
END
Please help, thanks in advance
请帮忙，提前致谢
Schema:
架构：
Employee
--------
Name, varchar(50)
Department, varchar (50)
EmployeeResult
--------------
Name, varchar(50)
Salary, int
Department, varchar (50)
回答by cmsjr
You want to take advantage of the insertedlogical table that is available in the context of a trigger. It matches the schema for the table that is being inserted to and includes the row(s) that will be inserted (in an update trigger you have access to the insertedand deletedlogical tables which represent the the new and original data respectively.)
您希望利用在触发器上下文中可用的插入逻辑表。它与要插入的表的架构相匹配，并包括将要插入的行（在更新触发器中，您可以访问分别代表新数据和原始数据的插入和删除的逻辑表。）
So to insert Employee / Department pairs that do not currently exist you might try something like the following.
因此，要插入当前不存在的员工/部门对，您可以尝试以下操作。
CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
Begin
Insert into Employee (Name, Department)
Select Distinct i.Name, i.Department
from Inserted i
Left Join Employee e
on i.Name = e.Name and i.Department = e.Department
where e.Name is null
End
回答by HLGEM
cmsjr had the right solution. I just wanted to point out a couple of things for your future trigger development. If you are using the values statement in an insert in a trigger, there is a stong possibility that you are doing the wrong thing. Triggers fire once for each batch of records inserted, deleted, or updated. So if ten records were inserted in one batch, then the trigger fires once. If you are refering to the data in the inserted or deleted and using variables and the values clause then you are only going to get the data for one of those records. This causes data integrity problems. You can fix this by using a set-based insert as cmsjr shows above or by using a cursor. Don't ever choose the cursor path. A cursor in a trigger is a problem waiting to happen as they are slow and may well lock up your table for hours. I removed a cursor from a trigger once and improved an import process from 40 minutes to 45 seconds.
cmsjr 有正确的解决方案。我只是想为你未来的触发器开发指出一些事情。如果您在触发器的插入中使用 values 语句，则很有可能您做错了事情。触发器为每批插入、删除或更新的记录触发一次。因此，如果在一批中插入 10 条记录，则触发器会触发一次。如果您要引用插入或删除中的数据并使用变量和值子句，那么您只会获取这些记录之一的数据。这会导致数据完整性问题。您可以通过使用基于集合的插入（如上面的 cmsjr 所示）或使用光标来解决此问题。永远不要选择光标路径。触发器中的游标是一个等待发生的问题，因为它们很慢并且很可能会锁定您的表数小时。
You may think nobody is ever going to add multiple records, but it happens more frequently than most non-database people realize. Don't write a trigger that will not work under all the possible insert, update, delete conditions. Nobody is going to use the one record at a time method when they have to import 1,000,000 sales target records from a new customer or update all the prices by 10% or delete all the records from a vendor whose products you don't sell anymore.
您可能认为没有人会添加多条记录，但它发生的频率比大多数非数据库人员意识到的要多。不要编写在所有可能的插入、更新、删除条件下都不起作用的触发器。当他们必须从新客户导入 1,000,000 条销售目标记录或将所有价格更新 10% 或删除您不再销售其产品的供应商的所有记录时，没有人会使用一次一条记录的方法。
回答by deepak
check this code:
检查此代码：
CREATE TRIGGER trig_Update_Employee ON [EmployeeResult] FOR INSERT AS Begin
Insert into Employee (Name, Department)
Select Distinct i.Name, i.Department
from Inserted i
Left Join Employee e on i.Name = e.Name and i.Department = e.Department
where e.Name is null
End
