Feb 4, 2014

Transaction management in SQL -- Sybase example

Q. How do you perform transaction management in SQL stored procedures?A. Handled with begin, commit, and rollback 'tran' commands in Sybase server.

Q. Why is it important?A. It is important to leave the database in a consistent state.

Q. Can you give an example?A. For example, as demonstrated below, if you are going to delete 21 records from a database table, you can use transaction management to ensure that a GIVEN COUNT say 21 records are either deleted or rolled back. Partially deleting records can leave the database in inconsistent state.

In Sybase, @@rowcount variable returns the number of rows affected by the query. This post also demonstrates a production ready script with proper print statements, transaction management, etc to perform a basic DELETE operation. Same thing is true for insert and update operations.

Q. Do you require any other script if the above deletion causes unexpected issue?A. Yes, you need a rollback script with 21 insert statements to revert the changes. In other words script to put the deleted data back.

Q. Is there a smarter way to generate the insert script or will you type them in one by one?A. There is a smarter way using a select statement to generate all the 21 insert statements with the following query.