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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now