Transcript Batches,and Scripts,

Batches, Scripts,
Transactions-SQL Server
7
Batches
A batch is a set of Transact-SQL
statements that are interpreted together
by SQL Server.
They are submitted together, and the end
of the batch is detected by usage of the
keyword GO.
SELECT au_id, au_lname FROM authors
SELECT pub_id, pub_name FROM
publishers
INSERT INTO publishers VALUES
(`9998','SAMS Publishing', 'Seattle',
'WA','USA')
GO
Batches follow several rules.
All of the SQL statements are compiled
together.
If there is a error anywhere in the batch,
the entire batch is cancelled.
If you were to modify the previous set
of SQL statements and introduce an
error, you would get an error message
from SQL Server, and none of the
statements would run.
For example:
SELECT au_id, au_lname FROM
authors
SELECT pub_id, pub_name FROM
publishers
INSERT INTO notable VALUES
(`9998','SAMS Publishing', 'Seattle',
'WA','USA')
GO
Msg 208, Level 16, State 1 Invalid
object name `notable‘
There is no such database object
named notable.
Some statements can be combined in a
batch, while other statements are
restricted.
The following CREATE statements can
be bound together within a single batch.
•CREATE DATABASE
•CREATE TABLE
•CREATE INDEX
These CREATE statements cannot be
combined with others.
•CREATE RULE
•CREATE TRIGGER
•CREATE PROCEDURE
•CREATE DEFAULT
•CREATE VIEW
There are some additional rules for
batches.
•Check constraints can't be bound and
used with tables in the same batch.
•You cannot drop and then re-create an
object with the same name within a
single batch.
•You cannot alter a table and then use
the new columns within the same
batch.
•SET statements take effect only at the
beginning of the next batch. Because of
this, it's a good idea to always follow a
SET statement with the keyword GO.
•There is a limit of 128KB for the size of
a batch. This limit doesn't apply to
WRITETEXT and UPDATETEXT
statements.
A CREATE TABLE with a check
constraint can be in a batch, but if you
attempt to insert data that would require
checking, the constraint is not enforced.
Data that violates the check constraint,
will be inserted anyway. An insert in a
new batch will produce the expected
error, however.
The following statements would fail with
the error that table2 already exists in the
database.
DROP TABLE table2
CREATE TABLE table2 (col1 INT NOT
NULL)
Scripts
A script is simply a set of one or more
batches.
Scripts typically are executed as part of
some unit of work that needs to be
accomplished, such as a data load or
database maintenance.
Example of a script.
SELECT au_id, au_lname FROM
authors
SELECT pub_id, pub_name FROM
publishers
INSERT publishers VALUES
(`9997','SAMS Publishing', 'Seattle',
'WA','USA')
GO
SELECT * FROM stores
GO
DELETE publishers WHERE pub_id =
`9997'
GO
Note that batches and scripts
don't necessarily have
anything to do with
transactions
Transactions
There are two types of transactions:
explicit and implicit.
Explicit transactions are transactions
that are manually configured.
Reserved words are used to indicate
the beginning and end of explicit
transactions.
These reserved words include
BEGIN TRANSACTION,
COMMIT TRANSACTION,
ROLLBACK TRANSACTION,
SAVE TRANSACTION.
Explicit Transactions
BEGIN TRAN
UPDATE authors SET city = `San Jose'
WHERE name = `Smith'
INSERT titles VALUES (`BU1122',
'Teach Yourself SQL Server in 21
days','business','9998',$35.00,
$1000.00,10,4501, `A great book!')
SELECT * from titleauthor
COMMIT TRAN
To cancel transactions.
BEGIN TRAN
Delete titles where type = `business'
IF @@ERROR > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
The ROLLBACK TRAN statement will
cancel the transaction completely.
Any work that was done in the
transaction up to that point will be rolled
back, or canceled.
break points can be placed within a
transaction.
Selectively roll back to those points.
BEGIN TRAN
UPDATE table1 SET col1 = 5 WHERE
col2 = 14
SAVE TRAN savepoint1
INSERT table2 values (1401,'book
review','a1201',9900)
IF @@error > 0 ROLLBACK TRAN
savepoint1
DELETE table3 WHERE col1 > 1401
IF @@error > 0 ROLLBACK TRAN
ELSE
COMMIT TRAN
ExampleThe following succeeds in inserting one
row into the table, even though there is
an error in the transaction (duplicate
primary key value)
create procedure r as
declare @p varchar(20);
create table a
(k int,
constraint fg primary key(k));
SELECT @p = 'M';
begin transaction @p;
insert into a values(1);
insert into a values(1);
commit transaction @p;
So, the transaction is responsible for
instigating the ROLLBACK
A transaction must specifically request
ROLLBACK-it is not automatic.
e.g.
create procedure r as
declare @p varchar(20);
create table a
(k int,
constraint fg primary key(k));
SELECT @p = 'M';
begin transaction @p;
insert into a values(1);
insert into a values(1);
IF @@error > 0 ROLLBACK
commit transaction @p;
Triggers, Transactions
and Rollback
Triggers can instigate ROLLBACK’s so
the integrity of the database is not
dependant on transactions controlling
their own ROLLBACK’s
If a ROLLBACK TRANSACTION is
issued in a trigger:
·All data modifications made to that
point in the current transaction are
rolled back, including any that were
made by the trigger.
·The trigger continues executing any
remaining statements after the
ROLLBACK statement. If any of these
statements modify data, the
modifications are not rolled back. No
nested triggers are fired by the
execution of these remaining
statements.
·The statements in the batch after the
statement that fired the trigger are not
executed.
ROLLBACK TRANSACTION
statements in stored procedures do
not affect subsequent statements in
the batch that called the procedure;
subsequent statements in the batch
are executed.
statements in triggers terminate the
batch containing the statement that
fired the trigger; subsequent
statements in the batch are not
executed.
When a constraint is violated in an
INSERT, UPDATE, or DELETE
statement, the statement is terminated.
However, the transaction (even if the
statement is part of an explicit
transaction) continues to be
processed.
The ROLLBACK TRANSACTION
statement is used by checking the
@@ERROR system function.
If a table has FOREIGN KEY or CHECK
CONSTRAINTS and triggers, the
constraint conditions are evaluated
before the trigger is executed.
Summary
SQL operations can be
encapsulated in batches.
Batches can be grouped into
scripts.
Transactions can be produced
explicitly.
Great care has to be taken over the
rules governing the execution of
batches, scripts and transactions.
Transaction are responsible for instigating
rollbacks.
Triggers are capable of instigating
rollbacks.
Great care has to be taken over the Rules
governing the execution of triggers.