SQL Server 2008 - Help writing simple INSERT Trigger

Question

This is with 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:

(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)

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 

Please help, thanks in advance

Schema:

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

Answer 1

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