Transactions are a powerful data integrity tool that many beginning database developers might not be aware of. Transactions are essentially a mechanism for grouping together one or more database updates so that SQL Server Express can treat them as a single entity. When a transaction is committed, all the update operations the transaction contains are written (or "committed") to the database. If a transaction is rolled back before it's been committed, all the transaction's update operations are undone and the database remains unchanged.
Using transactions will help you preserve database integrity, especially when you're updating multiple related tables, such as an order header table and an order detail table. When you group your updates of the related tables into a transaction, you ensure that all the tables are updated together. Even more important, if you encounter an error, the transaction ensures that you can roll back all the changes, preventing you from accidentally leaving orphaned rows in one table or another. For example, rolling back a transaction that contained both order header and order detail updates ensures the all the updates to both tables are removed and that there are no order detail rows that don't have a related order header row.
You start a transaction with the BEGIN TRANSACTION statement. You save the data using the COMMIT TRANSACTION statement, and you undo a transaction with the ROLLBACK TRANSACTION statement. The sample code below first creates a temporary table named #MyTempTable, then starts a new transaction that inserts a row into #MyTempTable. (I've given the transaction a name--InsertData--but naming a transaction is optional.) The code then commits that transaction and starts another transaction (which I've named DeleteData) that deletes a row. The code then rolls back the second transaction, leaving the table unchanged.
CREATE TABLE #MyTempTable
(
Column1 INT,
Column2 VARCHAR(20)
)
BEGIN TRANSACTION InsertData;
INSERT INTO #MyTempTable (Column1, Column2)
VALUES (1, 'Test Data');
COMMIT TRANSACTION InsertData;
GO
-- New row is added
SELECT * FROM #MyTempTable
BEGIN TRANSACTION DeleteData;
DELETE FROM #MyTempTable WHERE Column1 = 1
ROLLBACK TRANSACTION DeleteData;
GO
-- Table is unchanged
SELECT * FROM #MyTempTable
Although you typically wouldn't use transactions for a single-line update as I've done here, they are essential for complex systems that update multiple related tables. Rolling back the transaction rolls back the entire group of updates, and that just might save your bacon.
End of Article
Register
