Trigger vs Two Inserts

[dfrazell]

I have a table that new customers are added to through a stored procedure. When a new customer is added to the Customer table their customer ID also needs to be added to the Customer Summary table. Should I just add a second insert in my stored procedure for the Customer Summary table or should this be handled with a trigger on record insert? I'm using SQL2000.

Thanks.

 

[RiverGuy]

My choice would be to insert into both tables in a single transaction.

 

[jbenson001]

Either way is fine, however, most people forget about triggers when debugging if a problem comes up. I would agree with RiverGuy and just have 2 inserts in a single transaction.

 

[SQLSister]

This depends on the situation. Must the second record always be created and is the data to create the second record available from the first insert? If yes to both, then use a trigger to preserve data integrity. If no to the second question, you have no choice but to do two inserts. If no to the first, it is probably best to do two inserts, although the trigger won't hurt anything if the anser is no but it is ok to alawys have a record.

"NOTHING is more important in a database than integrity." ESquared

 

[dfrazell]

1. The second record must always be created.
2. I can't do the second insert until I know the CustID from the first insert. The CustID is an auto-increment and is known once the first insert is complete.

 

[SQLSister]

But what else do you need for the second insert besides the custID? if that is all you need, then create the record from the trigger. If you need additional information from the user interface you will need to write a second insert after getting the scope_Identity() value from the first insert becasue the trigger wouldn't have the information available to make the second insert. (Whatever you do, do not use @@identity to get this value or you will eventually have data integrity problems.)


"NOTHING is more important in a database than integrity." ESquared