Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2005-01-16 : 09:09:56
|
| I have a trigger on Orderdetails logging deletions into a log table. Basically, it logs the customer, charge, units, items, payments and adjustments. The prob is that the Payments table also has a delete trigger, because it synchronizes the paid and adjusted amounts in OrderDetails with the actual payments. Denormalized for performance. There is a primary/foreign key relationship between OrderDetails and Payments, so before deleting from OrderDetails, its payments must be deleted, which of course fires its (Payments) triggers, and the paid and adjusted columns in OrderDetails are updated to 0. So when the logging trigger on OrderDetails is fired after deletion, it no longer has the true paid and adjusted amounts.I'm using SQL 7, but due to upgrading in the near future, will be more than glad to accept a SQL2K solution.Thanks for any ideas.Sarah Berger MCSD |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-16 : 14:16:31
|
| With this kind of setup, the best thing would be to move the logic into one common procedure that handles the entire process correctly. If you cannot do this, then use a column on the Payments table that just indicates deletion status. Set the column to 1 if the deletions are being done for history. The triggers will need to look at the status column to determine appropriate action.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
|
|
|
|
|
Deletion logging