Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
267 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
Get row count of current SQL query 8 59
Upgrading SQL 2005 Express to 2008 R2 Express 31 130
Restrict result set 1 39
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 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