Solved

How to create a trigger to loop through one table and insert those values plus a variable into another table

Posted on 2007-11-28
2
265 Views
Last Modified: 2010-04-21
Here's the scenario. I have an application and the result I want is that when a new employee is created, a trigger executes and adds all tasks from tbltask table into a tblTaskCompletion table for the new employee.

1. Jane Doe is added to tblEmployee
2. Insert trigger needs created, inserting multiple records into tblTaskCompletion - Within tblTask there are 50 new tasks that require completion for this employee - these must be added to tblTaskComletion (EmployeeID, TaskID) - EmployeeID was just created and upon insert, add all tasks from tblTask to tblTaskCompletion with new EmployeeID

I could do this within my application, but feel performance wise, this is the best route to go doing it via SQL Server. I'm using SQL Server 2005 by the way.

Thank you in advance for your assistance!!

0
Comment
Question by:cmcormick
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 20365538
Loop is the wrong idea.  A trigger is the right idea, though.

create Trigger dbo.tblEmployee_ITrig
  On tblEmployee After Insert
AS
Insert Into tblTaskCompletion (EmployeeID, TaskID)
(Select I.EmployeeID, T.TaskID From Inserted I, tblTask T)
0
 

Author Closing Comment

by:cmcormick
ID: 31411415
Abolutely perfect! Thank you!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question