Triggers

A trigger is a special type of stored procedure that is automatically invoked whenever the data in the table is modified. It is the last line of defense against bad data and destruction of data integrity. No tables can be modified without firing triggers. Triggers can be written to enforce referential integrity though the CML database relies on declarative referential integrity using FOREIGN KEY constraints. One limitation of FOREIGN KEY constraints is the lack of support for cascading activities (adds, deletes, and updates of tables).

Referential integrity between tables in a database is enforced by default when you create a relationship in your database diagram. The CML database diagram shows relationships among tables.

Triggers are most useful when the features supported by constraints and bound objects cannot meet the functional needs of the application.

The CML application uses few triggers for the following reasons:

• The application does not cascade changes or deletes through related tables in the database.
• The application does not require customized messages and complex error handling. Standardized system error messages, such as the message the user receives when attempting to duplicate a barcode value in the item table when adding a new item record, are appropriate for the CML application. Bound objects and constraints, like the UNIQUE constraint on barcode, can only communicate errors through standardized system error messages.
• The application does not validate a column value against a column in another table.
• The application never finds the difference between the state of a table before and after a data modification and takes action based on that difference.

The CML developers decided to define two triggers on the request table of the CML database, one for insert and one for delete activities. No constraint, default, or rule can perform the complex task of maintaining the request queue, though constraints and defaults should be used instead of triggers whenever they provide all the needed functionality.

The CML application relies on the fm_tr_queue_dequeue and fm_tr_queue_enqueue triggers to keep the request queue (req_queue_ord) properly sequenced.

The fm_tr_queue_dequeue trigger is on the delete event of the request table. Whenever request number 1 for a specified bib# in the request table is deleted, the remaining requests in that queue are decremented by 1, in effect making the request that was number 2 number 1 and so forth.

The fm_tr_queue_enqueue trigger is on the insert event of the request table. Whenever a record is inserted in the table, the combination of bib# and borrower# is checked for uniqueness. The CML business rules do not allow a borrower to repeat a request for a library title.

Note  The fm_tr_queue_dequeue trigger is not an example of an appropriate use of triggers. A better way to enforce this rule would be to create a unique constraint for borrower# and bib# on the request table. See Database Triggers in Lessons Learned for more details about inappropriate and appropriate use of triggers.

As the CML application development progressed, the developers saw a need for additional triggers. A new column, n_items, was added to the title table. This column tracks the number of items assigned to the title. The insert trigger, fm_tr_items_add, and the delete trigger, fm_tr_items_del, are written on the item table to maintain this count in the title table.