I do not know if this can be done. I know with a stored procedure it can be accomplished. I want to know if it can be accomplished with a Trigger. Ok, lets say there are 3 tables - tableA, tableB, and tableC. The result is to get data from tableA and tableB into tableC.
I call a stored procedure and in this stored procedure I pass in the values for TableA to perform an insert. I am also inserting into TableB and getting the ID by SCOPE_IDENTITY.
So lets say I need the following fileds TableA.Name, TableA.Address, and TableC.ResultsID (these tables are not joined in anyway and the value of TableC.ResultsID will be obtained using SCOPE_IDENTITY).
The question is if I put a trigger on TableA to pass the data to TableC -- how do I get the TableC.ResultID data in the Trigger?
Hope this makes sense.