Grant Fritchey (4/27/2012)
I'm not so sure I'd use a trigger. Those things are notorious performance and maintenance headaches.What about using the OUTPUT clause. You can output the IDs generated from an INSERT statement into a temp table or table variable and then use those values for the INSERT into the child table. That's the method I'll use when I need to do something like this.
I agree with Grant here, if you have control over the process doing the insert, such as within a stored procedure.
A trigger, my second thought, would be your fallback if you do not have control over the process doing the insert, such as when someone has direct access to the tables and can issue inserts to the parent and may not follow a documented procedure to also populate the child table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato