A little background information, hopefully it is pertinent. I am building a small web application to manage Home Buyer's To-Dos/communications for one of our clients who is a Home Builder. I did not design the database, the DBMS is Newstar and it is a homebuilding software package. It uses a SQL database(which they designed) and one of the modules they provide is a communications piece but it was poorly written and that brings me to where I am now. My client wants a list of To Do's to be assigned to each new buyer. I created the List in the TaskList Table because the Task Types that are in the BuyerTask table are hard coded so you can not customize it. What I need is a trigger that once a ContractDate which is a field in the BuyerSelections database has been entered, the trigger will populate the Tasks from the TaskList table into the BuyerTasks table. Once I can automate the creation of these Tasks I can use the grid's I've created to display the new tasks for each home buyer and allow them to complete/enter notes and make changes as needed.
Again, I have three tables that are a part of this scenario.
BuyerSelections BuyerTasks and TaskList(The only one I created)
1. Sales Agent enters a Contract Date in Newstar for a new homebuyer.
2. Trigger populates all Tasks from the TaskList table into the BuyerTasks table. The key is to associate the tasks with the new home buyer. So the trigger needs to insert BuyerSelections.ProspectID, BuyerSelections.SelectionID and BuyerSelections.SalesOfficeID into BuyerTasks table for each one of the tasks that gets created.
The two columns that need to get inserted into the BuyerTasks table from the TaskList table are TaskList.TaskType and TaskList.Subject. The columns already exist inside of BuyerTasks and are being used by the current communications module inside of Newstar.
ContractDate in BuyerSelections gets entered for ProspectID 0054564
From the same row/record where the BuyerSelections.ContractDate was entered the Trigger needs to insert the list of Tasks from TaskList into the BuyerTasks table. TaskList.TaskType and TaskList.Subject need to be inserted along with the values from BuyerSelections.ProspectID, BuyerSelections.SelectionID and BuyerSelections.SalesOfficeID into the BuyerTask table so that all Tasks will be associated with the correct Home Buyer/Prospect and Sales Office. Can anyone here help me build a trigger like this?