Java Reference
In-Depth Information
SELECT @NewCost = cost FROM Inserted
SELECT @OldCost = cost FROM Deleted
IF @NewCost > (@OldCost * 1.15)
ROLLBACK Transaction;
The SQL ROLLBACK command used in this code snippet is one of the Transaction
Management commands. Transaction management and the SQL ROLLBACK
command are discussed in the
next section
.
Using transaction management commands with UPDATE
Transaction management
refers to the capability of a relational database
management system to execute database commands in groups, known as
transactions
. A transaction is a group or sequence of commands, all of which must be
executed in order and all of which must complete successfully. If anything goes
wrong during the transaction, the database management system allows the entire
transaction to be cancelled or "rolled back." If, on the other hand, it completes
successfully, the transaction can be saved to the database or "committed."
In the SQL code snippet below, there are two update commands. The first attempts to
set the cost of Corn Flakes to $3.05, and the cost of Shredded Wheat to $2.15. Prior
to attempting the update, the cost of Corn Flakes is $2.05, so the update clearly
violates the FifteenPctRule trigger defined above. Since both updates are contained
within a single transaction, the ROLLBACK command in the FifteenPctRule trigger
will execute, and neither update will take effect.
BEGIN transaction;
UPDATE Inventory
SET Cost = 3.05
WHERE Name = 'Corn Flakes';
UPDATE Inventory
SET Cost = 2.15
WHERE Name = 'Shredded Wheat';
COMMIT transaction;
Although all SQL commands are executed in the context of a transaction, the
transaction itself is usually transparent to the user unless the AUTOCOMMIT option is
turned off. Most databases support the AUTOCOMMIT option, which tells the
RDBMS to commit all commands individually as they are executed. This option can
be used with the SET command:
SET AUTOCOMMIT [ON | OFF] ;
By default, the SET AUTOCOMMIT ON command is executed at startup, telling the
RDBMS to commit all statements automatically as they are executed. When you start
