{"id":3212,"date":"2022-04-11T10:29:52","date_gmt":"2022-04-11T03:29:52","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3212"},"modified":"2022-04-11T10:32:45","modified_gmt":"2022-04-11T03:32:45","slug":"sql-server-transaction","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-transaction\/","title":{"rendered":"SQL Server Transaction"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn about SQL Server transactions and how to use T-SQL to execute transactions. <\/p>\n\n\n\n

Introduction to the SQL Server Transaction #<\/a><\/h2>\n\n\n\n

A transaction is a single unit of work that typically contains multiple T-SQL statements. <\/p>\n\n\n\n

If a transaction is successful, the changes are committed to the database. However, if a transaction has an error, the changes have to be rolled back.<\/p>\n\n\n\n

When executing a single statement such as INSERT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code>, SQL Server uses the autocommit transaction. In this case, each statement is a transaction.<\/p>\n\n\n\n

To start a transaction explicitly, you use the BEGIN TRANSACTION<\/code> or BEGIN TRAN<\/code> statement first:<\/p>\n\n\n

BEGIN<\/span> TRANSACTION<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, execute one or more statements including INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code>.<\/p>\n\n\n\n
Finally, commit the transaction using the COMMIT<\/code> statement:<\/p>\n\n\n
COMMIT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Or roll back the transaction using the ROLLBACK<\/code> statement:<\/p>\n\n\n
ROLLBACK<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the sequence of statements for starting a transaction explicitly and committing it:<\/p>\n\n\n
-- start a transaction<\/span>\nBEGIN<\/span> TRANSACTION<\/span>;\n\n-- other statements<\/span>\n\n-- commit the transaction<\/span>\nCOMMIT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server Transaction example #<\/a><\/h2>\n\n\n\n
We’ll create two tables: invoices<\/code> and invoice_items<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> invoices (\n  id<\/span> int<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  customer_id int<\/span> NOT<\/span> NULL<\/span>,\n  total decimal<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span> CHECK<\/span> (total >= 0<\/span>)\n);\n\nCREATE<\/span> TABLE<\/span> invoice_items (\n  id<\/span> int<\/span>,\n  invoice_id int<\/span> NOT<\/span> NULL<\/span>,\n  item_name varchar<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n  amount decimal<\/span>(10<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> CHECK<\/span> (amount >= 0<\/span>),\n  tax decimal<\/span>(4<\/span>, 2<\/span>) NOT<\/span> NULL<\/span> CHECK<\/span> (tax >= 0<\/span>),\n  PRIMARY KEY<\/span> (id<\/span>, invoice_id),\n  FOREIGN<\/span> KEY<\/span> (invoice_id) REFERENCES<\/span> invoices (id<\/span>)\n\tON<\/span> UPDATE<\/span> CASCADE<\/span>\n\tON<\/span> DELETE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The invoices<\/code> table stores the header of the invoice while the invoice_items<\/code> table stores the line items. The total field in the invoices<\/code> table is calculated from the line items.<\/p>\n\n\n\n
The following example uses the BEGIN TRANSACTION<\/code> and COMMIT<\/code> statements to create a transaction:<\/p>\n\n\n
BEGIN<\/span> TRANSACTION<\/span>;\n\nINSERT<\/span> INTO<\/span> invoices (customer_id, total)\nVALUES<\/span> (100<\/span>, 0<\/span>);\n\nINSERT<\/span> INTO<\/span> invoice_items (id<\/span>, invoice_id, item_name, amount, tax)\nVALUES<\/span> (10<\/span>, 1<\/span>, 'Keyboard'<\/span>, 70<\/span>, 0.08<\/span>),\n       (20<\/span>, 1<\/span>, 'Mouse'<\/span>, 50<\/span>, 0.08<\/span>);\n\nUPDATE<\/span> invoices\nSET<\/span> total = (SELECT<\/span>\n  SUM<\/span>(amount * (1<\/span> + tax))\nFROM<\/span> invoice_items\nWHERE<\/span> invoice_id = 1<\/span>);\n\nCOMMIT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
First, start a transaction explicitly using the BEGIN TRANSACTION<\/code> statement:<\/p>\n\n\n
BEGIN<\/span> TRANSACTION<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, insert a row into the invoices<\/code> table and return the invoice id:<\/p>\n\n\n
DECLARE<\/span> @invoice TABLE<\/span> (\n  id<\/span> int<\/span>\n);\n\nDECLARE<\/span> @invoice_id int<\/span>;\n\nINSERT<\/span> INTO<\/span> invoices (customer_id, total)\nOUTPUT<\/span> INSERTED.id INTO<\/span> @invoice\nVALUES<\/span> (100<\/span>, 0<\/span>);\n\nSELECT<\/span>\n  @invoice_id = id<\/span>\nFROM<\/span> @invoice;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, insert two rows into the invoice_items<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> invoice_items (id<\/span>, invoice_id, item_name, amount, tax)\nVALUES<\/span> (10<\/span>, @invoice_id, 'Keyboard'<\/span>, 70<\/span>, 0.08<\/span>),\n       (20<\/span>, @invoice_id, 'Mouse'<\/span>, 50<\/span>, 0.08<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, calculate the total using the invoice_items<\/code> table and update it to the invoices<\/code> table:<\/p>\n\n\n
UPDATE<\/span> invoices\nSET<\/span> total = (\n    SELECT<\/span> SUM<\/span>(amount * (1<\/span> + tax))\n    FROM<\/span> invoice_items\n    WHERE<\/span> invoice_id = @invoice_id\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, commit the transaction using the COMMIT<\/code> statement:<\/p>\n\n\n
COMMIT<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
  • Use the BEGIN TRANSACTION<\/code> statement to start a transaction explicitly.<\/li>
  • Use the COMMIT<\/code> statement to commit the transaction and ROLLBACK<\/code> statement to roll back the transaction.<\/li><\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t