7.2.1. Transactions

Transactions resemble a stop-light system. Incoming traffic halts at a red light for a period while traffic traveling together in the other direction moves through the intersection.

A practical example might be a banking application where a transfer from savings to checking involves changing the balance in the savings and then changing the balance in the checking. This application might have these two SQL statements:

# Deduct $100 from the $110 in the savings account
UPDATE account
SET balance = 10.00
WHERE id = 1234
# Add $100 to the $55 in the checking account
UPDATE account
SET balance = 155.00
WHERE id = 5678

Between the two updates, another transaction could be issued by another client that checks the balance of the checking and savings accounts to see if there is enough money for a check. If that were to happen, the check would bounce. Worse still, if the server crashed between the two updates, your client would have just lost $100 to the bit bucket.

7.2.2. Triggers

Triggers are a feature closely related to transactions. To carry the traffic analogy one step further, imagine a police officer sitting on a hill overlooking the intersection. Should one of the cars do something illegal, the officer enters traffic and pursues the offending car.

A trigger is one or more SQL statements stored in the database that are executed whenever some predefined event occurs. Triggers are a method of automating monitoring tasks. Whenever a certain condition is met, the trigger can act upon data or simply report that the triggering event took place.

7.2.3. Stored Procedures

At their simplest, stored procedures are simply one or more SQL statements stored in the database under some simple name to encapsulate a certain behavior. In the example of the account transfer mentioned above, you could simply store those two SQL statements as a single stored procedure called "transfer." Your application passes the stored procedure the two account numbers and an amount and it executes those two SQL statements in a single transaction.

On a more complex level, stored procedures may add to the basic SQL syntax so that it looks more like a traditional programming language. Oracle's PL/SQL and Sybase/Microsoft's TransactSQL are two examples of such SQL extensions. You may often hear of people using these kinds of stored procedures to "put the business logic in the database."

7.2.4. Subselects

The standard SQL SELECT statement enables complete access to all of the data stored within a table -- if you know what you are looking for. Unless you are willing to retrieve the entire contents of the table, the most basic form of SELECT requires you to input at least a portion of the data you wish to retrieve. For example, SELECT name FROM friends WHERE name LIKE 'B%' requires you to know at least one letter of the name you are looking for. More specifically, what if you wanted to know who was making more than the average salary? The query would look something like:

SELECT name FROM people WHERE salary > ???

Greater than what? You do not have any idea what the average salary is unless you select that! You need to take the value of SELECT AVG(salary) FROM people and plug it into the earlier query. A subselect enables you to do this:

SELECT name 
FROM people
WHERE salary > (SELECT AVG(salary) FROM people)

7.2.5. Objects