What is a Trigger?

A trigger was initially defined as a "predefined database procedure, conditionally or unconditionally succeeding or preceding other database operations automatically" (K.P Eswaran, "Specifications, Implementations and Interactions of a Trigger Subsystem in an Integrated Database System," IBM Research Report, RJ1820, 1976). This does not imply stored procedures per se --it means procedural code, or a sequence of operations coded in a mixture of SQL and programming language statements. A trigger is therefore procedural processing logic, stored in the database and executed automatically by the DBMS server under specific conditions. The word "automatic" is very important -- applications or users do not activate the triggers, they are executed automatically when the applications or users perform specific operations on the database. A trigger has the following components:

* Constraint: The integrity constraint or business rule enforced by the trigger -- in other words, the "purpose" of the trigger. Practically speaking, this should appear in the header of the trigger, which should be reflected in its name. For example, the "Positive Opening Balance" constraint requires that all new accounts must have non-negative balances.

* Event: A specific situation occurring in the database, which indicates when the constraint should be enforced. The event is said to be "raised" when the situation occurs. The event is often specified in two ways: as a state change on the database (for example, an insert into the Accounts table) and as an optional predicate condition used to filter out some of the state changes (for example, only those inserts into the Accounts table with a negative value for the Balance column). Specific moments are also very interesting events. Note that "event" in this generic definition should not be confused with the database event alerters supported by some DBMSs such as CA-OpenIngres.

* Action: A procedure or a sequence of procedural operations that implement the processing logic required to enforce the constraint. For example, the action must enforce the business rule that accounts may not have negative opening balances. This can be done by refusing the insert operation if the opening balance is negative, or by replacing the negative balance with zero and inserting an entry in a journaling table. The implied "if" condition highlights another point: conventional database manipulation operations (that is, SQL select, insert, update, and delete statements) are usually too limited to implement the required actions. They must be extended with procedural constructs such as iteration (while, for, and repeat) and conditional (if and case) statements. A trigger is therefore an event that "fires" an action to enforce a constraint.

Advantages of Triggers

The most attractive feature of triggers is that they are stored and executed in the database. This has the following advantages:

* The triggers always fire when the associated events occur. Application developers do not have to remember to include the functionality in each application, and users cannot bypass the triggers through interactive tools. (But every skilled DBA knows how to bypass the triggers on his or her system.) Most DBMSs have some mechanism to bypass the trigger, either by temporarily deactivating the triggers or by using a "trace point" or some similar mechanism.

* Triggers are administered centrally. They are coded once, tested once, and then centrally enforced for all the applications accessing the database. The triggers are usually controlled, or at least audited, by a skilled DBA. The result is that the triggers are implemented efficiently.

* The central activation and processing of triggers fits the client/
server architecture well. A single request from a client can result in a whole sequence of checks and subsequent operations performed on the database. The data and operations are not "dragged" across the network between the client and the server.

Because triggers are so powerful, they must be managed well and they must be used correctly. Inefficient triggers can bring the database server to its knees, due to the amount of work fired off in the database. Incorrect triggers can corrupt the integrity of the data.

What is Trigger Used For?

Triggers are extremely powerful constructs and can be used for various purposes, for example:

* Integrity control: You can use triggers to implement domain integrity, column integrity, referential integrity, and unconventional integrity constraints. I will address the declarative vs. the do-it-yourself (that is, using triggers) approaches to integrity control in my next column.

* Business rules: You can use triggers to centrally enforce business rules. Business rules are constraints that are enforced over the relationships between tables or between different rows in the same table. For example, the sum of the amounts of the InvoiceItems rows must add up to the total on the row in the Invoices table for the corresponding invoice -- that is, if your organization's standards and/or DBAs let you have denormalized derived attributes in your physical data model.

* Application logic: You can use triggers to enforce business logic centrally, for example, to insert rows automatically in the Orders and OrderItems tables when the QuantityOnHand value in the Stocks table drops below a given threshold. Business rules could be formalized and could actually be defined declaratively, if only the declarative syntax allowed it; but application logic requires more complex functionality than can be specified declaratively.

* Security: You can use triggers to check value-based security constraints. When an operation is performed on a sensitive table, the trigger fires to check that the operation is allowed for the user. For example, you may only insert a row in a table if the department column contains the value of your own department. In most systems, however, you cannot use triggers to restrict the data that is visible to users. The only exception I came across is the Progress Database Server, in which you can define triggers to fire on select operations. This makes it possible to inspect or filter the data a user is about to see. For example, you can restrict a user to only retrieve the details of orders placed for his department.

* Audit trails: Triggers can insert records into an audit trail table to log all the operations on sensitive tables. The problem with this approach is that most trigger actions are under transactional control. When an operation is rolled back, all its triggered operations are also rolled back. The triggers will therefore only record the effects of successful operations. When an unsuccessful operation is rolled back, the audit trail entry of that operation will also be rolled back. The audit trail will therefore not contain attempted threats at violating data integrity constraints or security restrictions.

* Replication: Many DBMS vendors and consultants have implemented replicators using triggers as the recording mechanism. In essence, when the replicated tables change, the triggers fire and record the changes in buffer tables. A replication server then propagates the operations from the buffer tables to the various target databases. (It is, however, not quite as simple as it sounds.) In this situation, the transactional control on the triggers is extremely useful, as you only want to replicate successfully completed transactions.

* Updatable views: In Borland's InterBase, you can define triggers on views and tables. You can then use the view triggers to propagate the actions performed on the view to the underlying base table(s). You can use this extremely powerful feature to update theoretically non-updatable views. For example, the triggers on a view that does not contain the keys of the underlying tables can query the key values of the underlying tables and can then perform the necessary operations.

The use of triggers is only limited by the functionality provided by the particular DBMS and, of course, your imagination and innovative spirit.