If you doing updates (add, edit, delete) to several tables at the same time and interdependent among the updates, you are encourage to use "transaction" in order to ensure successful update to all tables.
Some articles in the Internet will teach you to initiate the transcation command from the ASP codes. BUT, that doesn't mean it is a correct method to use. It is working fine in the ideal environment and infrastructure. Look at the example below.
What happen at:
| ASP | SQL Server |
| Initiate begin transaction | Mark the start point in the transaction log to inform the server to expect transaction activities |
| Add record to Table1 | Server updates the Table1 |
| Connection down due to bandwidth "clog" |
| Delete from Table1 (not successful) | Server still expecting the subsequent query(s) OR commit/rollback command |
| Commit transaction | Server still expecting further command(s). Worst case, resource locking will be occured. |
Uncommit transaction will maintain in the server (more accurate - at the transaction log) as incomplete updates. When you query at the Table1, you will see the updates (add one new record). BUT, this update will not last forever! Once the DBA "flush" the transaction log or restart the server, the record in Table1 will be gone! It is no magic but SQL Server is designed such a way by Microsoft. You will get more knowledge about this if you do some research about its architecture.
The example above doesn't mean Transaction is not good, but unproper use of it will create "disaster" to your application. If you really need to use Transaction, try to write all the queries in the Stored Procedures. ASP only send a single command to the server to execute the stored procedure, so the possibility of lost connection during the updates will be very minimum. In the stored procedures, use global variable @@error to detect the any error during the executions. Look at the example below:
CREATE PROCEDURE dbo.proc_test_transaction
AS
BEGIN TRANSACTION
INSERT INTO TABLE1 (FL1, FL2) VALUES (0,1)
DELETE FROM TABLE2 WHERE FL3 = 0
....
IF @@ERROR > 1 /* if no error, @@ERROR will be 0*/
BEGIN
ROLLBACK TRANSACTION
RETURN /* exit from the procedure immediately*/
END
COMMIT TRANSACTION /* without this command, server will not commit the transaction, unless the server specially configured*/
GO
