I am learning more about SQL as I work on this Database project. Today I encountered Transactions, and after looking at what they are and how they work, my first thought was, "Shit, I need to change my Procedures to use these."
Throughout today I have been looking on here, and Google is trying to find out when it is appropriate to use a Transaction, on how to correctly write the syntax for one. I honestly haven't found much, so I'm essentially writing this so that I can see if I am implementing these properly, and for anyone who may be looking for something like this in the future (hello Google).
So I've mostly been throwing Transactions into any Procedure I have created which either inserts data or deletes data. It seems to me that if I am querying data, not manipulating it, transactions aren't necessary. If I am wrong in this thinking, please feel free to illuminate me.
So this is an example of me using a Transaction in a Procedure that is Deleting Data:
CREATE PROCEDURE DeleteOrder @OrderNum VARCHAR(20) ) AS BEGIN DECLARE @OrderID INT IF(EXISTS(SELECT order_id FROM purchase_orders WHERE order_number = @OrderNum)) BEGIN SELECT @OrderID = order_id FROM purchase_orders WHERE order_number = @OrderNum END ELSE RETURN 1 BEGIN TRANSACTION DelOrder BEGIN TRY DELETE FROM order_parts WHERE order_id = @OrderID DELETE FROM purchase_price_logs WHERE order_id = @OrderID DELETE FROM purchase_orders WHERE order_id = @OrderID COMMIT TRANSACTION DelOrder END TRY BEGIN CATCH ROLLBACK TRANSACTION DelOrder RETURN 1 END CATCH END
The first question I have is; Is my syntax correct? Am I implementing the Transaction appropriately? Is there a way you would code what I am trying to do differently? For that last question, it's more about the whole Procedure than just the Transaction segment.
Finally, I have a question I thought of while writing this post. Is it possible to have said a 'Master Rollback' or 'Master Commit' procedure, where while you have a connection going on with the database, no transactions are committed until your 'Master Commit' Procedure is called? Something like.
MasterCommit For Each Transaction Commit Transaction
With something similar being the same for Rolling back. I know of the existence of SavePoints, so is this where they'd be helpful? More so with the Rollbacks than the Commits I suppose.