Databases Reference
In-Depth Information
The other two conditions are easier to enforce because they can be han-
dled by table constraints. You could use a unique constraint to enforce unique-
ness on the order number and a foreign key constraint to enforce the rela-
tionship. If either is violated, the database server generates an error. However,
instead of just having the transaction fail and roll back, you could include logic
FOR EXAMPLE
Transactions from the Command Line
Let's talk about explicit transactions from the command line. When using SQL
Server 2005, considering that it defaults to autocommit transactions, you might
wonder why you would need, or even want, to use explicit transactions.
One possible reason is simple. We all sometimes make mistakes. When
connected to a database server with administrator permissions and making
direct changes to table data, the consequences of our mistakes could be far-
reaching. Say you're doing some cleanup work in the database and you're
deleting some old customer records. You run:
delete customer where custid = 'AJA1244'
delete customer where custid = 'BX54T4'
delete customer
At some point, immediately after you press F5, you realize your mis-
take. You immediately run:
ROLLBACK TRAN
The query window responds with:
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN
TRANSACTION.
You've lost all of the data in the customer table. The situation isn't hope-
less. You have backups (hopefully) and you can restore the customer table
from the most recent backup. That means an interruption to user opera-
tions, lost time, lost money, and possibly a lost job (yours).
What if you ran the following, instead:
begin tran
delete customer where custid = 'AJA1244'
delete customer where custid = 'BX54T4'
delete customer
This would give you a chance to verify your changes before committing the
transaction and if you do have a mistake, like the typo in the last DELETE
statement, you can roll the changes back and start again. Now, if you still
end up committing the changes without first verifying, you're on your own.
