SQL Transactions

Intro

Transactions ensure that either all commands execute or no commands execute

Consider a banking transaction where money is transfered from one account to another

Syntax

BEGIN TRANSACTION
  UPDATE savings
  ...
  UPDATE checking
  ...
  COMMIT TRANSACTION  

ROLLBACK command is an undo

SELECT * FROM A.B.C.D

-- A is the SERVER
-- B is the DATABASE
-- C is the SCHEMA (like a directory)
-- D is the TABLE

The number of periods between A and D define what A is:

A.D --> A is the Schema
A..D --> A is the Database
A...D --> A is the Server

Schema

DBO is the default Schema that is used if one is not explicitly defined

Transactions

-- HOSTNAME...tableName

BEGIN TRANSACTION
  UPDATE CALGARY...accounts
    SET balance = balance - 1000
    WHERE acct_no = 1
  UPDATE EDMONTON...accounts
    SET balance = balance + 300
    WHERE acct_no = 1
  UPDATE LETHBRIDGE...accounts
    SET balance = balance + 700
    WHERE acct_no = 1
COMMIT TRANSACTION    

INSERT

INSERT INTO customers
  (customerid, companyname, contactname)
VALUES ('0103', 'Home Depot', 'George')

INSERT SELECT

SELECT INTO

SELECT productname AS products

DELETE

Run a SELECT to ensure that the data you want to delete is the correct data then replace it with DELETE FROM

DELETE FROM northwind
  WHERE customerid = '43246'

UPDATE

UPDATE products
  SET unitprice = (unitprice * 1.1)