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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server 2008 - Help writing simple INSERT Trigger

Examples related to sql

Examples related to sql-server-2008

Examples related to insert

Examples related to triggers