Multiple triggers for each table and operation.
In most relational databases, there are three operations that can be monitored: insert, update and delete. This means a user can write a special stored procedure known as trigger that will be invoked automatically by the engine before the action takes place. Of course, the action is related to an specific table. The common implementations allow only one trigger for operation on the same table, for example, only one trigger can be defined before an update takes place on table "tbl", only one trigger for the same table before an insert takes place and the same for the delete operation.
Interbase allows several triggers per operation on the same table. There's an special clause to specify the order in which the triggers for the same table and operation are invoked, so you are in complete control of the sequence. If two triggers have the same precedence on the same operation over the same table, then they are executed in a random order or in alpha order (using their names) depending upon the Interbase version.
This facility has several advantages: first, if your validations are huge and independent, you don't need to write a monster trigger; you can write multiple triggers instead and this design improves modularity. Second, the precedence of triggers let you make the most critical and lightweight validations first so if they are not met, then you reject the operation and no more triggers will be processed because the operation is being aborted.
If this doesn't suffice for your needs, IB distinguish between BEFORE and AFTER operations for each trigger. Your validation triggers normally use BEFORE, so they can stop an operation by aborting the command that invoked the trigger implicitly. Of course, if an operation is aborted, all operations that led to it are undone, too. Your AFTER triggers serve mainly two purposes: filling information in log tables and posting events. These two activities fit nicely here because your AFTER triggers only get invoked if the action they are bound to was successfully carried out. Compare this flexibility against other RDBMS products that only offer only one trigger per table/operation and it always fires before the operation.
For these features to have real merit, you must be able to tell what's the value of a field before the operation and what will be the value after the operation. IB provides two qualifiers, NEW and OLD to help you make the difference. Prefix fields' names with NEW or OLD plus a dot and you kill any ambiguity that may appear. To summarize:
 |
In BEFORE/AFTER UPDATE triggers, you have OLD.field and NEW.field |
|
In BEFORE/AFTER INSERT triggers, you have NEW.field |
|
In BEFORE/AFTER DELETE triggers, you have OLD.field |
The way you use to abort processing is raising an exception, a separated topic in this list of features. Both trigger and stored procedures can use exceptions but only triggers can use NEW/OLD qualifiers, because only triggers are tied to one table and one event. You cannot share triggers among several events or tables but you can have the generic processing in a separate stored procedure that you call from the triggers. Because stored procedures aren't aware of current operations over tables, you pass the field values as parameters to the called stored procedure in this case.