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:
(Jack,200,Sales)
(Jane,300,Marketing)
(John,400,Engineering)
(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)
推荐答案
您要利用可用的插入逻辑表在触发条件下。它与要插入的表的模式匹配,并包括将要插入的行(在更新触发器中,您可以访问已插入和已删除逻辑表分别代表新数据和原始数据。)
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.)
因此,要插入当前不存在的Employee / Department对,可以尝试以下操作。
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屋!