SQL Server/T-SQL Tutorial/Transaction/COMMIT

A script that performs a test before committing the transaction

5>
6>
7>
8> create table Billings (
9>     BankerID           INTEGER,
10>     BillingNumber      INTEGER,
11>     BillingDate        datetime,
12>     BillingTotal       INTEGER,
13>     TermsID            INTEGER,
14>     BillingDueDate     datetime ,
15>     PaymentTotal       INTEGER,
16>     CreditTotal        INTEGER
17>
18> );
19> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2>
3>
4> create table Bankers(
5>    BankerID             Integer,
6>    BankerName           VARCHAR(20),
7>    BankerContactLName   VARCHAR(20),
8>    BankerContactFName   VARCHAR(20),
9>    BankerCity           VARCHAR(20),
10>    BankerState          VARCHAR(20),
11>    BankerZipCode        VARCHAR(20),
12>    BankerPhone          VARCHAR(20)
13> )
14> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat",  "Paris",    "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina",   "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee",  "Toronto",  "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary",  "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act",  "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat",  "Orland",   "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee",  "Wisler",   "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee",  "Ticker",   "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2> BEGIN TRAN
3> DELETE Billings
4> WHERE BankerID = 34
5> IF @@ROWCOUNT > 1
6>     BEGIN
7>         ROLLBACK TRAN
8>         PRINT "More Billings than expected. Deletions rolled back."
9>     END
10> ELSE
11>     BEGIN
12>         DELETE Bankers
13>         WHERE BankerID = 34
14>         COMMIT TRAN
15>         PRINT "Deletions committed to the database."
16>     END
17> GO
(0 rows affected)
(0 rows affected)
(0 rows affected)
Deletions committed to the database.
1>
2> drop table Bankers;
3> drop table Billings;
4> GO
1>