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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL to Update Table Dynamically 2 61
SQL Agent Timeout 5 69
Help with simplifying SQL 6 54
Using this function 4 38
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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