Posted by Erland Sommarskog on 10/01/09 11:28

laurenq uantrell (laurenquantrell@hotmail.com) writes:
> Erland. Thanks for the help and the good avice. The COMMIT in the
> middle in this situation is because the first insert puts a new row of
> data into a table, the second insert records the row/column info into a
> row in a history table. In this situation, I want to be sure that the
> new record is recorded in the primary data table even if the history is
> not. If there is a failure on the second insert, I don't want to
> rollback the first insert, even though the records might not end up in
> sync (original and history) this would be corrected hopefully the next
> time the record is updated.

Good. I just wanted to make sure that it's on purpose.

(I remember a system I worked with looooong ago. There was a stored
proecdure that filled up a table, and it was one long transaction.
Unfortunately, it tended to fill up the transaction log. (This was
Sybase 4.x, no autogrow.) My colleagues found the fix - they inserted
some COMMIT/BEGIN. Oh well.)

> I have considered and rejected using triggers for this purpose even
> though that has been suggested.

Well, that would buy you a transaction for the current and history table.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]