SQL Server 2008 - 帮助编写简单的 INSERT 触发器 [英] SQL Server 2008 - Help writing simple INSERT Trigger

问题描述

这是与 Microsoft SQL Server 2008 一起使用的.

This is with Microsoft SQL Server 2008.

我有 2 个表，Employee 和 EmployeeResult，我正在尝试在 EmployeeResult 上编写一个简单的 INSERT 触发器来执行此操作 - 每次对 EmployeeResult 执行 INSERT 时，例如:

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:

(杰克，200，销售)(简，300，营销)(约翰，400，工程)

(Jack, 200, Sales) (Jane, 300, Marketing) (John, 400, Engineering)

它应该查找姓名、部门条目对，例如

It should look up for the Name, Department entry pairs, such as

(杰克，销售)，(简，营销)，(约翰，工程)

(Jack, Sales), (Jane, Marketing), (John, Engineering)

在 Employee 表中，如果这样的员工不存在，应将其插入到 Employee 表中.

within the Employee table, and if such an employee does not exist, should insert that into the Employee table.

我所拥有的是关于如何修复???"的未知数:

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

请帮忙，提前致谢

架构:

Employee
--------
Name, varchar(50)
Department, varchar (50)

EmployeeResult
--------------
Name, varchar(50)
Salary, int
Department, varchar (50)

推荐答案

您希望利用在触发器上下文中可用的插入逻辑表.它与要插入的表的架构相匹配，并包括将要插入的行(在更新触发器中，您可以访问 inserted 和 deleted分别代表新数据和原始数据的逻辑表.)

You want to take advantage of the inserted logical 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 inserted and deleted logical 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

这篇关于SQL Server 2008 - 帮助编写简单的 INSERT 触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！