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)