Active Database Concepts and Triggers

Active Database Concepts and Triggers

Rules that specify actions that are automatically triggered by certain events have been considered important enhancements to database systems for quite some time. In fact, the concept of triggers—a technique for specifying certain types of active rules—has existed in early versions of the SQL specification for relational databases and triggers are now part of the SQL-99 and later standards. Commercial relational DBMSs—such as Oracle, DB2, and Microsoft SQLServer—have various versions of triggers available. However, much research into what a general model for active databases should look like has been done since the early models of triggers were proposed. In Section 26.1.1 we will present the general concepts that have been pro-posed for specifying rules for active databases. We will use the syntax of the Oracle commercial relational DBMS to illustrate these concepts with specific examples, since Oracle triggers are close to the way rules are specified in the SQL standard. Section 26.1.2 will discuss some general design and implementation issues for active databases. We give examples of how active databases are implemented in the STAR-BURST experimental DBMS in Section 26.1.3, since STARBURST provides for many of the concepts of generalized active databases within its framework. Section 26.1.4 discusses possible applications of active databases. Finally, Section 26.1.5 describes how triggers are declared in the SQL-99 standard.

1. Generalized Model for Active Databases and Oracle Triggers

The model that has been used to specify active database rules is referred to as the Event-Condition-Action (ECA) model. A rule in the ECA model has three components:

        1. The event(s) that triggers the rule: These events are usually database update operations that are explicitly applied to the database. However, in the general model, they could also be temporal events² or other kinds of external events.

        2. The condition that determines whether the rule action should be executed: Once the triggering event has occurred, an optional condition may be evaluated. If no condition is specified, the action will be executed once the event occurs. If a condition is specified, it is first evaluated, and only if it evaluates to true will the rule action be executed.

        3. The action to be taken: The action is usually a sequence of SQL statements, but it could also be a database transaction or an external program that will be automatically executed.

Let us consider some examples to illustrate these concepts. The examples are based on a much simplified variation of the COMPANY database application from Figure 3.5 and is shown in Figure 26.1, with each employee having a name (Name), Social

Security number (Ssn), salary (Salary), department to which they are currently assigned (Dno, a foreign key to DEPARTMENT), and a direct supervisor (Supervisor_ssn, a (recursive) foreign key to EMPLOYEE). For this example, we assume that NULL is allowed for Dno, indicating that an employee may be temporar-ily unassigned to any department. Each department has a name (Dname), number (Dno), the total salary of all employees assigned to the department (Total_sal), and a manager (Manager_ssn, which is a foreign key to EMPLOYEE).

Notice that the Total_sal attribute is really a derived attribute, whose value should be the sum of the salaries of all employees who are assigned to the particular department. Maintaining the correct value of such a derived attribute can be done via an active rule. First we have to determine the events that may cause a change in the value of Total_sal, which are as follows:

        1. Inserting (one or more) new employee tuples

        2. Changing the salary of (one or more) existing employees

        3. Changing the assignment of existing employees from one department to another

        4. Deleting (one or more) employee tuples

In the case of event 1, we only need to recompute Total_sal if the new employee is immediately assigned to a department—that is, if the value of the Dno attribute for the new employee tuple is not NULL (assuming NULL is allowed for Dno). Hence, this would be the condition to be checked. A similar condition could be checked for event 2 (and 4) to determine whether the employee whose salary is changed (or who is being deleted) is currently assigned to a department. For event 3, we will always execute an action to maintain the value of Total_sal correctly, so no condition is needed (the action is always executed).

The action for events 1, 2, and 4 is to automatically update the value of Total_sal for the employee’s department to reflect the newly inserted, updated, or deleted employee’s salary. In the case of event 3, a twofold action is needed: one to update the Total_sal of the employee’s old department and the other to update the Total_sal of the employee’s new department.

The four active rules (or triggers) R1, R2, R3, and R4—corresponding to the above situation—can be specified in the notation of the Oracle DBMS as shown in Figure 26.2(a). Let us consider rule R1 to illustrate the syntax of creating triggers in Oracle.

The CREATE TRIGGER statement specifies a trigger (or active rule) name Total_sal1 for R1. The AFTER clause specifies that the rule will be triggered after the events that trigger the rule occur. The triggering events—an insert of a new employee in this example—are specified following the AFTER keyword.

The ON clause specifies the relation on which the rule is specified—EMPLOYEE for R1. The optional keywords FOR EACH ROW specify that the rule will be triggered once for each row that is affected by the triggering event.

The optional WHEN clause is used to specify any conditions that need to be checked after the rule is triggered, but before the action is executed. Finally, the action(s) to be taken is (are) specified as a PL/SQL block, which typically contains one or more SQL statements or calls to execute external procedures.

The four triggers (active rules) R1, R2, R3, and R4 illustrate a number of features of active rules. First, the basic events that can be specified for triggering the rules are the standard SQL update commands: INSERT, DELETE, and UPDATE. They are spec-ified by the keywords INSERT, DELETE, and UPDATE in Oracle notation. In the case of UPDATE, one may specify the attributes to be updated—for example, by writing UPDATE OF Salary, Dno. Second, the rule designer needs to have a way to refer to the tuples that have been inserted, deleted, or modified by the triggering event. The key-words NEW and OLD are used in Oracle notation; NEW is used to refer to a newly inserted or newly updated tuple, whereas OLD is used to refer to a deleted tuple or to a tuple before it was updated.

Thus, rule R1 is triggered after an INSERT operation is applied to the EMPLOYEE relation. In R1, the condition (NEW.Dno IS NOT NULL) is checked, and if it evaluates to true, meaning that the newly inserted employee tuple is related to a department, then the action is executed. The action updates the DEPARTMENT tuple(s) related to the newly inserted employee by adding their salary (NEW.Salary) to the Total_sal attribute of their related department.

Rule R2 is similar to R1, but it is triggered by an UPDATE operation that updates the SALARY of an employee rather than by an INSERT. Rule R3 is triggered by an update to the Dno attribute of EMPLOYEE, which signifies changing an employee’s assign-ment from one department to another. There is no condition to check in R3, so the action is executed whenever the triggering event occurs. The action updates both the old department and new department of the reassigned employees by adding their salary to Total_sal of their new department and subtracting their salary from Total_sal of their old department. Note that this should work even if the value of Dno is NULL, because in this case no department will be selected for the rule action.

It is important to note the effect of the optional FOR EACH ROW clause, which sig-nifies that the rule is triggered separately for each tuple. This is known as a row-level trigger. If this clause was left out, the trigger would be known as a statement-level trigger and would be triggered once for each triggering statement. To see the differ-ence, consider the following update operation, which gives a 10 percent raise to all employees assigned to department 5. This operation would be an event that triggers rule R2:

UPDATE   EMPLOYEE

SET        Salary = 1.1 _* Salary

WHERE Dno = 5;

Because the above statement could update multiple records, a rule using row-level semantics, such as R2 in Figure 26.2, would be triggered once for each row, whereas a rule using statement-level semantics is triggered only once. The Oracle system allows the user to choose which of the above options is to be used for each rule. Including the optional FOR EACH ROW clause creates a row-level trigger, and leaving it out creates a statement-level trigger. Note that the keywords NEW and OLD can only be used with row-level triggers.

As a second example, suppose we want to check whenever an employee’s salary is greater than the salary of his or her direct supervisor. Several events can trigger this rule: inserting a new employee, changing an employee’s salary, or changing an employee’s supervisor. Suppose that the action to take would be to call an external procedure inform_supervisor,⁶ which will notify the supervisor. The rule could then be written as in R5 (see Figure 26.2(b)).

Figure 26.3 shows the syntax for specifying some of the main options available in Oracle triggers. We will describe the syntax for triggers in the SQL-99 standard in Section 26.1.5.

Figure 26.3

A syntax summary for specifying triggers in the Oracle system (main options only).

<trigger>        ::=        CREATE TRIGGER <trigger name>

                        ( AFTER I BEFORE ) <triggering events> ON <table name>

                        [ FOR EACH ROW ]

                        [ WHEN <condition> ]

                        <trigger actions> ;

<triggering events>  ::= <trigger event> {OR <trigger event> }

<trigger event>                     ::= INSERT I DELETE I UPDATE [ OF <column name> { , <column name> } ]

<trigger action>                    ::= <PL/SQL block>

2. Design and Implementation Issues for Active Databases

The previous section gave an overview of some of the main concepts for specifying active rules. In this section, we discuss some additional issues concerning how rules are designed and implemented. The first issue concerns activation, deactivation, and grouping of rules. In addition to creating rules, an active database system should allow users to activate, deactivate, and drop rules by referring to their rule names. A deactivated rule will not be triggered by the triggering event. This feature allows users to selectively deactivate rules for certain periods of time when they are not needed. The activate command will make the rule active again. The drop command deletes the rule from the system. Another option is to group rules into named rule sets, so the whole set of rules can be activated, deactivated, or dropped. It is also useful to have a command that can trigger a rule or rule set via an explicit PROCESS RULES command issued by the user.

The second issue concerns whether the triggered action should be executed before, after, instead of, or concurrently with the triggering event. A before trigger executes the trigger before executing the event that caused the trigger. It can be used in applications such as checking for constraint violations. An after trigger executes the trigger after executing the event, and it can be used in applications such as maintaining derived data and monitoring for specific events and conditions. An instead of trigger executes the trigger instead of executing the event, and it can be used in applications such as executing corresponding updates on base relations in response to an event that is an update of a view.

A related issue is whether the action being executed should be considered as a separate transaction or whether it should be part of the same transaction that triggered the rule. We will try to categorize the various options. It is important to note that not all options may be available for a particular active database system. In fact, most commercial systems are limited to one or two of the options that we will now discuss.

Let us assume that the triggering event occurs as part of a transaction execution. We should first consider the various options for how the triggering event is related to the evaluation of the rule’s condition. The rule condition evaluation is also known as rule consideration, since the action is to be executed only after considering whether the condition evaluates to true or false. There are three main possibilities for rule consideration:

        Immediate consideration. The condition is evaluated as part of the same transaction as the triggering event, and is evaluated immediately. This case can be further categorized into three options:

        Evaluate the condition before executing the triggering event.

        Evaluate the condition after executing the triggering event.

        Evaluate the condition instead of executing the triggering event.

Deferred consideration. The condition is evaluated at the end of the trans-action that included the triggering event. In this case, there could be many triggered rules waiting to have their conditions evaluated.

        Detached consideration. The condition is evaluated as a separate transaction, spawned from the triggering transaction.

The next set of options concerns the relationship between evaluating the rule condition and executing the rule action. Here, again, three options are possible: immediate, deferred, or detached execution. Most active systems use the first option. That is, as soon as the condition is evaluated, if it returns true, the action is immediately executed.

The Oracle system (see Section 26.1.1) uses the immediate consideration model, but it allows the user to specify for each rule whether the before or after option is to be used with immediate condition evaluation. It also uses the immediate execution model. The STARBURST system (see Section 26.1.3) uses the deferred consideration option, meaning that all rules triggered by a transaction wait until the triggering transaction reaches its end and issues its COMMIT WORK command before the rule conditions are evaluated.⁷

Another issue concerning active database rules is the distinction between row-level rules and statement-level rules. Because SQL update statements (which act as triggering events) can specify a set of tuples, one has to distinguish between whether the rule should be considered once for the whole statement or whether it should be considered separately for each row (that is, tuple) affected by the statement. The SQL-99 standard (see Section 26.1.5) and the Oracle system (see Section 26.1.1) allow the user to choose which of the options is to be used for each rule, whereas STAR-BURST uses statement-level semantics only. We will give examples of how statement-level triggers can be specified in Section 26.1.3.

One of the difficulties that may have limited the widespread use of active rules, in spite of their potential to simplify database and software development, is that there are no easy-to-use techniques for designing, writing, and verifying rules. For exam-ple, it is quite difficult to verify that a set of rules is consistent, meaning that two or more rules in the set do not contradict one another. It is also difficult to guarantee termination of a set of rules under all circumstances. To illustrate the termination

problem briefly, consider the rules in Figure 26.4. Here, rule R1 is triggered by an INSERT event on TABLE1 and its action includes an update event on Attribute1 of TABLE2. However, rule R2’s triggering event is an UPDATE event on Attribute1 of TABLE2, and its action includes an INSERT event on TABLE1. In this example, it is easy to see that these two rules can trigger one another indefinitely, leading to non-termination. However, if dozens of rules are written, it is very difficult to determine whether termination is guaranteed or not.

If active rules are to reach their potential, it is necessary to develop tools for the design, debugging, and monitoring of active rules that can help users design and debug their rules.

3. Examples of Statement-Level Active Rules in STARBURST

We now give some examples to illustrate how rules can be specified in the STAR-BURST experimental DBMS. This will allow us to demonstrate how statement-level rules can be written, since these are the only types of rules allowed in STARBURST.

The three active rules R1S, R2S, and R3S in Figure 26.5 correspond to the first three rules in Figure 26.2, but they use STARBURST notation and statement-level semantics. We can explain the rule structure using rule R1S. The CREATE RULE statement specifies a rule name—Total_sal1 for R1S. The ON clause specifies the relation on which the rule is specified—EMPLOYEE for R1S. The WHEN clause is used to spec-ify the events that trigger the rule.⁸ The optional IF clause is used to specify any conditions that need to be checked. Finally, the THEN clause is used to specify the actions to be taken, which are typically one or more SQL statements.

In STARBURST, the basic events that can be specified for triggering the rules are the standard SQL update commands: INSERT, DELETE, and UPDATE. These are specified by the keywords INSERTED, DELETED, and UPDATED in STARBURST notation. Second, the rule designer needs to have a way to refer to the tuples that have been modified. The keywords INSERTED, DELETED, NEW-UPDATED, and OLD-UPDATED are used in STARBURST notation to refer to four transition tables (relations) that include the newly inserted tuples, the deleted tuples, the updated tuples before they were updated, and the updated tuples after they were updated, respectively. Obviously, depending on the triggering events, only some of these transition tables may be available. The rule writer can refer to these tables when writing the condition and action parts of the rule. Transition tables contain tuples of the same type as those in the relation specified in the ON clause of the rule—for R1S, R2S, and R3S, this is the EMPLOYEE relation.

In statement-level semantics, the rule designer can only refer to the transition tables as a whole and the rule is triggered only once, so the rules must be written differently than for row-level semantics. Because multiple employee tuples may be

R1S:   CREATE RULE Total_sal1 ON EMPLOYEE

            WHEN            INSERTED   

            IF         EXISTS( SELECT * FROM INSERTED WHERE Dno IS NOT NULL  )

            THEN UPDATE        DEPARTMENT AS D

                        SET    D.Total_sal = D.Total_sal +

                                    ( SELECT SUM (I.Salary) FROM INSERTED AS I WHERE D.Dno = I.Dno )

                        WHERE         D.Dno IN ( SELECT Dno FROM INSERTED );

R2S:   CREATE RULE Total_sal2 ON EMPLOYEE

            WHEN            UPDATED     ( Salary  )

            IF         EXISTS          ( SELECT * FROM NEW-UPDATED WHERE Dno IS NOT NULL  )

                        OR EXISTS   ( SELECT * FROM OLD-UPDATED WHERE Dno IS NOT NULL  )

            THEN UPDATE        DEPARTMENT AS D

                        SET    D.Total_sal = D.Total_sal +

                                    ( SELECT SUM (N.Salary) FROM NEW-UPDATED AS N

                                    WHERE D.Dno = N.Dno ) –

                                    ( SELECT SUM (O.Salary) FROM OLD-UPDATED AS O

                                    WHERE D.Dno = O.Dno )

                        WHERE         D.Dno IN ( SELECT Dno FROM NEW-UPDATED ) OR

                                    D.Dno IN ( SELECT Dno FROM OLD-UPDATED  );

R3S:   CREATE RULE Total_sal3 ON EMPLOYEE

WHEN            UPDATED     ( Dno  )

THEN UPDATE        DEPARTMENT AS D

            SET    D.Total_sal = D.Total_sal +

                        ( SELECT SUM (N.Salary) FROM NEW-UPDATED AS N

                        WHERE D.Dno = N.Dno )

            WHERE         D.Dno IN ( SELECT Dno FROM NEW-UPDATED );

            UPDATE        DEPARTMENT AS D

            SET    D.Total_sal = Total_sal –

                        ( SELECT SUM (O.Salary) FROM OLD-UPDATED AS O

                        WHERE D.Dno = O.Dno )

            WHERE         D.Dno IN ( SELECT Dno FROM OLD-UPDATED );

Figure 26.5

Active rules using statement-level semantics in STARBURST notation.

inserted in a single insert statement, we have to check if at least one of the newly inserted employee tuples is related to a department. In R1S, the condition

EXISTS (SELECT _* FROM INSERTED WHERE Dno IS NOT NULL )

is checked, and if it evaluates to true, then the action is executed. The action updates in a single statement the DEPARTMENT tuple(s) related to the newly inserted employee(s) by adding their salaries to the Total_sal attribute of each related depart-ment. Because more than one newly inserted employee may belong to the same department, we use the SUM aggregate function to ensure that all their salaries are added.

Rule R2S is similar to R1S, but is triggered by an UPDATE operation that updates the salary of one or more employees rather than by an INSERT. Rule R3S is triggered by an update to the Dno attribute of EMPLOYEE, which signifies changing one or more employees’ assignment from one department to another. There is no condition in R3S, so the action is executed whenever the triggering event occurs.⁹ The action updates both the old department(s) and new department(s) of the reassigned employees by adding their salary to Total_sal of each new department and subtract-ing their salary from Total_sal of each old department.

In our example, it is more complex to write the statement-level rules than the row-level rules, as can be illustrated by comparing Figures 26.2 and 26.5. However, this is not a general rule, and other types of active rules may be easier to specify when using statement-level notation than when using row-level notation.

The execution model for active rules in STARBURST uses deferred consideration. That is, all the rules that are triggered within a transaction are placed in a set— called the conflict set—which is not considered for evaluation of conditions and execution until the transaction ends (by issuing its COMMIT WORK command). STARBURST also allows the user to explicitly start rule consideration in the middle of a transaction via an explicit PROCESS RULES command. Because multiple rules must be evaluated, it is necessary to specify an order among the rules. The syntax for rule declaration in STARBURST allows the specification of ordering among the rules to instruct the system about the order in which a set of rules should be considered. Additionally, the transition tables—INSERTED, DELETED, NEW-UPDATED, and OLD-UPDATED—contain the net effect of all the operations within the transaction that affected each table, since multiple operations may have been applied to each table during the transaction.

4. Potential Applications for Active Databases

We now briefly discuss some of the potential applications of active rules. Obviously, one important application is to allow notification of certain conditions that occur. For example, an active database may be used to monitor, say, the temperature of an industrial furnace. The application can periodically insert in the database the temperature reading records directly from temperature sensors, and active rules can be written that are triggered whenever a temperature record is inserted, with a condition that checks if the temperature exceeds the danger level, and results in the action to raise an alarm.

Active rules can also be used to enforce integrity constraints by specifying the types of events that may cause the constraints to be violated and then evaluating appropriate conditions that check whether the constraints are actually violated by the event or not. Hence, complex application constraints, often known as business rules, may be enforced that way. For example, in the UNIVERSITY database application, one rule may monitor the GPA of students whenever a new grade is entered, and it may alert the advisor if the GPA of a student falls below a certain threshold; another rule may check that course prerequisites are satisfied before allowing a student to enroll in a course; and so on.

Other applications include the automatic maintenance of derived data, such as the examples of rules R1 through R4 that maintain the derived attribute Total_sal when-ever individual employee tuples are changed. A similar application is to use active rules to maintain the consistency of materialized views (see Section 5.3) whenever the base relations are modified. Alternately, an update operation specified on a view can be a triggering event, which can be converted to updates on the base relations by using an instead of trigger. These applications are also relevant to the new data ware-housing technologies (see Chapter 29). A related application maintains that replicated tables are consistent by specifying rules that modify the replicas when-ever the master table is modified.

5. Triggers in SQL-99

Triggers in the SQL-99 and later standards are quite similar to the examples we dis-cussed in Section 26.1.1, with some minor syntactic differences. The basic events that can be specified for triggering the rules are the standard SQL update commands: INSERT, DELETE, and UPDATE. In the case of UPDATE, one may specify the attributes to be updated. Both row-level and statement-level triggers are allowed, indicated in the trigger by the clauses FOR EACH ROW and FOR EACH STATEMENT, respectively. One syntactic difference is that the trigger may specify particular tuple variable names for the old and new tuples instead of using the keywords NEW and OLD, as shown in Figure 26.1. Trigger T1 in Figure 26.6 shows how the row-level trigger R2 from Figure 26.1(a) may be specified in SQL-99. Inside the REFERENCING clause, we named tuple variables (aliases) O and N to refer to the OLD tuple (before modification) and NEW tuple (after modification), respectively. Trigger T2 in Figure 26.6 shows how the statement-level trigger R2S from Figure 26.5 may be specified in SQL-99. For a statement-level trigger, the REFERENCING clause is used to refer to the table of all new tuples (newly inserted or newly updated) as N, whereas the table of all old tuples (deleted tuples or tuples before they were updated) is referred to as O.