Schema-Level Constraints and Triggers

Schema-Level Constraints and Triggers

The most powerful forms of active elements in SQL are not associated with specific tuples or components of tuples. These elements, called "triggers" and "assertions," are part of the database schema, on a par with the relations and views themselves.

●  An assertion is a boolean-valued SQL expression that must be true at all times.

●  A trigger is a series of actions that are associated with certain events, such as insertions into a particular  relation, and that are performed whenever these events arise.

While assertions are easier for the programmer to use, since they just require the programmer to state what must be true, triggers are the feature DBMS's usually provide as general-purpose, active elements. The reason is that it is very hard to implement assertions efficiently. The DBMS must deduce whether any given database modification could affect the truth of an assertion. Triggers, on the other hand, tell exactly when the DBMS needs to deal with them.


The SQL standard proposes a simple form of assertion (also called a "general constraint") that allows us to enforce any condition (expression that can follow WHERE). Like other schema elements, we declare an assertion with a CREATE statement. The form of an assertion is:

1. The keywords CREATE ASSERTION,
2. The name of the assertion,
3. The keyword CHECK, and
4. A parenthesized condition.

That is, the form of this statement is

    CREATE ASSERTION <name> CHECK (<condition>)

The condition in an assertion must be true when the assertion is created and must always remain true; any database modification whatsoever that causes it to become false will be rejected. Remember that the other types of CHECK constraints we have covered can be violated under certain conditions, if they involve subqueries.

There is a difference between the way we write tuple-based CHECK constraints and the way we write assertions. Tuple-based checks can refer to the attributes of that relation in whose declaration they appear. For example, in line (6) of "Tuple-Based CHECK Constraints" Figure 1 we used attributes gender and name without saying where they came from. They refer to components of a tuple being inserted or updated in the table MovieStar, because that table is the one being declared in the CREATE TABLE statement.

The condition of an assertion has no such privilege. Any attributes referred to in the condition must be introduced in the assertion, usually by mentioning their relation in a select-from-where expression. Since the condition must have a boolean value, it is normal to aggregate the results of the condition in some way to make a single true/false choice. For instance, we might write the condition as an expression producing a relation, to which NOT EXISTS is applied; that is, the constraint is that this relation is always empty. Alternatively, we might apply an aggregate operator like SUM to a column of a relation and compare it to a constant. For example, this way we could require that a sum always be less than some limiting value.

Example 1 : Assume we wish to require that no one can become the president of a studio unless their  net worth is at least $10,000,000. We declare an assertion to the effect that the set of movie studios with presidents having a net worth less than $10,000,000 is empty. This assertion involves the two relations

    MovieExec(name, address, cert#, netWorth)
    Studio(name, address, presC#)

Assertion guaranteeing rich studio presidents

The assertion is shown in Figure 1.

Incidentally, it is worth noting that even though this constraint involves two relations, we could write it as tuple-based CHECK constraints on the two relations rather than as a single assertion. For example, we can add to the CREATE TABLE statement of "Declaring Foreign-Key Constraints" Example 1 a constraint on Studio as shown in Figure 2.

A constraint on Studio mirroring an assertion

Note, nevertheless, that the constraint of Figure 2 will only be checked when a change to its relation, Studio occurs. It would not catch a situation where the net worth of some studio president, as recorded in relation MovieExec, dropped below $10,000,000. To get the full effect of the assertion, we would have to add another constraint to the declaration of the table MovieExec, requiring that the net worth be at least $10,000,000 if that executive is the president of a studio.

Example 2 : Here is another instance of an assertion. It involves the relation

    Movie(title, year, length, inColor, studioName, producerC#)

Comparison of Constraints

and says the total length of all movies by a given studio shall not exceed 10,000 minutes.

    CREATE ASSERTION SumLength CHECK (10000 >= ALL
          (SELECT SUM(1ength) FROM Movie GROUP BY studioName)

As this constraint involves only the relation Movie, it could have been expressed as a tuple-based CHECK constraint in the schema for Movie rather than as an assertion. That is, we could add to the definition of table Movie the tuple-based CHECK constraint

    CHECK (10000 >= ALL
         (SELECT SUM(1ength) FROM Movie GROUP BY studioName));

Notice that in principle this condition applies to every tuple of table Movie. However, it does not mention any attributes of the tuple explicitly, and all the work is done in the subquery.

Also observe that if implemented as a tuple-based constraint, the check would not be made on deletion of a tuple from the relation Movie. In this example, that difference causes no harm, since if the constraint was satisfied before the deletion, then it is surely satisfied after the deletion. On the other hand, if the constraint were a lower bound on total length, rather than an upper bound as in this example, then we could find the constraint violated had we written it as a tuple-based check rather than an assertion.

As a final point, it is possible to drop an assertion. The statement to do so follows the pattern for any database schema element:

    DROP ASSERTION <assertion name>

Event-Condition-Action Rules

Triggers, sometimes called event-condition-action rules or ECA rules, differ from the kinds of constraints discussed previously in three ways;

1. Triggers are only awakened when certain events, specified by the database programmer, occur. The sorts of events allowed are usually insert, delete, or update to a particular relation. Another kind of event allowed in many SQL systems is a transaction end (we mentioned transactions briefly in "Deferring the Checking of Constraints" and cover them with more detail in "Transactions in SQL").

2. Instead of immediately preventing the event that awakened it, a trigger tests a condition. If the condition does not hold, then nothing else associated with the trigger happens in response to this event.

3. If the condition of the trigger is satisfied, the action associated with the trigger is performed by the DBMS. The action may then prevent the event from taking place, or it could undo the event (e.g., delete the tuple inserted). In fact, the action could be any sequence of database operations, perhaps even operations not connected in any way to the triggering event.