Triggers

The constraints we've discussed to this pointNOT NULL, CHECK, FOREIGN KEY, PRIMARY KEY, and UNIQUEare known as declarative constraints. Triggers are not declarative because they contain procedural code. They're slightly less pure in the relational sense, and early DBMSs didn't support them, but today seven of the Big Eight support both declarative constraints and triggers.

Portability

The exception is MySQL, which supports only PRIMARY KEY and UNIQUE as declarative constraints, and doesn't support triggers at all. The gains shown throughout this section are for only seven DBMSs.

In our tests, we found that triggers are slower than declarative constraints. For example, firing this trigger with a compound SQL statement:

 CREATE TRIGGER Trigger1    AFTER INSERT ON Table1    REFERENCING NEW ROW AS New    FOR EACH ROW    BEGIN      IF        New.column1 NOT BETWEEN 1 AND 10      THEN        SIGNAL SQLSTATE = '23000'      END IF    END 

is slower than leaving the checking up to the CHECK constraint in this table definition:

 CREATE TABLE Table1 (    column1 SMALLINT,    CHECK (column1 BETWEEN 1 AND 10)    ...) GAIN: 4/7 

Portability

Because triggers were around long before the SQL Standard got around to requiring them, every DBMS uses slightly different syntax to define triggers. A discussion of the differences is beyond the scope of this book.

However, before glibly saying "declarative constraints are better than triggers," we should note that the trigger has flexibilities that might be more important to you than performance. For example, a trigger statement can log an incident and return a customized warning, while all a declarative constraint can do in the same instance is return an error with a fixed error message. An old saw, which we've never heard before, goesThe more inflexible the server is, the more adaptable the client must be.

Nevertheless, the trigger involves so many steps that it can't be fast. Consider the steps shown in Listing 10-3.

Listing 10-3 Steps involved in executing a trigger

 A trigger must: Make a savepoint. For each row: {   Perform "before each row" trigger code.   Copy the row as it exists before update, to a BEFORE IMAGE.   Update the row.   Copy the row as it exists after update, to an AFTER IMAGE.   } At end of statement (after constraints are checked): {   For each row: {     Perform "after each row" trigger code.     If (fatal error) restore to savepoint.   } Cleanup. } 

Listing 10-3 shows that a trigger does two "for each row" loops . In theory the DBMS doesn't have to do the second loop if the trigger is only "before each row." In practice, though, before- and after-triggers are equally fast.

At this time, there is support only for INSERT, UPDATE, and DELETE triggers (except with Informix, which also provides SELECT triggers). To make a SELECT trigger, you can employ a trick that works like thisSuppose you want to keep a log of each time that a row from Table1 , which contains the string WASHINGTON in a column named city , is retrieved. The solution is to mandate that all selections must go via a view of Table1 , called View1 , and to define View1 with an external function call. The SELECT statement would thus look like this:

 SELECT * FROM View1    WHERE city = 'WASHINGTON' 

The CREATE VIEW statement would look like this:

 CREATE VIEW View1 AS SELECT        Table1.*,        Function1(city) AS Function_return    FROM Table1 

And the CREATE FUNCTION statement would look like this:

 CREATE FUNCTION Function1    (parameter1 CHAR(10)) RETURNS SMALLINT    LANGUAGE C    PARAMETER STYLE GENERAL    NOT DETERMINISTIC    RETURN NULL ON NULL INPUT    NO SQL    EXTERNAL 

Finally, the external function code would look like Listing 10-4.

Listing 10-4 External function code for SELECT trigger

 short* __export __cdecl FUNCTION1 (char* c) {   static short return_value;   HANDLE FileHandle;   HANDLE MutexHandle;   unsigned long ret;   if (lstrcmp(c,"Washington")==0) {     MutexHandle=CreateMutex(0,TRUE,"MUTEX");     FileHandle=CreateFile(           "T",           GENERIC_WRITE,           0,           0,           OPEN_ALWAYS,           FILE_ATTRIBUTE_NORMAL,           0);     SetFilePointer(FileHandle,0,0,FILE_END);     WriteFile(FileHandle,"it happened",11,&ret,0);     CloseHandle(FileHandle);     CloseHandle(MutexHandle);     return_value=ret; }   else return_value=0;   return (&return_value); } 

The matter is not simpleyou must persuade the DBMS that the function is unpredictable and can change the column value. Otherwise, the DBMS will optimize the function call and only do it once. To persuade the DBMS not to optimize, write the function in a language the DBMS doesn't understand natively, make the column input/output (in fact the column isn't touched), and above all declare the function as NOT DETERMINISTIC.

A word of caution about NOT DETERMINISTIC functions. In real triggers, it is possible to call stored procedures with nondeterministic functions, but there is a peril. Our favorite one triggers an action that depends on the time of day, because "for some reason" time of day can't be used in a CHECK clause. Well, some people would think that reason is importantsuppose the database becomes corrupt, and the recovery procedure involves rerunning a log of transactions. In such a case, the recovery isn't run at the same time of day as the original transactionand thus a transaction that succeeded before the crash will fail during the recovery. Remember that some DBMSs handle functions in select lists when they open the cursor; others handle them when they fetch from the cursor.

The Bottom Line: Triggers

If speed is your only concern, use declarative constraints instead of triggers.

Use views with an external function call to write a SELECT trigger.

Declarative constraints differ from triggers in that constraint conditions guarantee that data always fulfills a specific requirement, while trigger conditions are enforced only when data changes. That is, constraints are passive requirements for correctness, while triggers are responses only to specific actions.

Because trigger checking happens after constraint checking, it's tricky to change the value of a table column within the trigger. That could lead to loops (because the change would cause another constraint check) or to subversions (because the change wouldn't go through the same checking that a regular data-change statement would go through). The SQL Standard has special rules that are designed to prevent such tricks.

Triggers fire on INSERT, UPDATE, or DELETE statements but not on " implied " inserts like the DBMS's nonstandard database LOAD statement and not on "implied" deletes like the DBMS's nonstandard table TRUNCATE statement.