We just saw in the previous example how “after” triggers can perform updates on other tables to automate management tasks such as logging. The capability to extend the reach of a DML event beyond the immediate context of the table and row that “own” the data has some important applications for managing difficult relationships.
Enforcing a Mandatory Relationship
A mandatory relationship exists when two tables are linked by a foreign key dependency and there must be at least one dependent row for each primary row. Since SQL does not provide a “mandatoriness” constraint, trigger logic is needed to enforce the “minimum of one child” rule, not only at creation time but also when dependent rows are deleted.
The following example outlines one way to use triggers to enforce this mandatory master -detail relationship. It assumes that the primary key of the master table is known by the application before the new row is posted. First, we create the two tables:CREATE TABLE MASTER (
When the application posts the rows for the master and detail tables, it will pass the detail rows first, with NULL in the foreign key column and the primary key value of the master in the column TEMP_FK.
Next, we need an exception that can be raised if an attempt is made to violate the mandatory rule and delete the last detail row. We also create a generator for the detail row.CREATE EXCEPTION CANNOT_DEL_DETAIL
This trigger tests the detail table after the new master record version has been written. It can “see” the details rows previously posted in the same transaction that have its primary key value (NEW.ID) in the TEMP_FK column. In the case of an update, rather than an insert, it can also identify any rows that it already “owns.” Any that meet the TEMP_FK condition get their foreign key filled and TEMP_FK is set to null.
If it finds no rows meeting these conditions, it inserts an “empty” detail row itself.SET TERM ^;
The detail table gets an automatically generated key:CREATE TRIGGER BI_DETAIL FOR DETAIL
This BEFORE DELETE trigger for the detail table will ensure that the row cannot be deleted if it is the only one.CREATE TRIGGER BD_DETAIL FOR DETAIL
Currently, we have the situation where the mandatory relationship is protected so well that, if an attempt is made to delete the master row, this trigger will cause the cascading delete to fail. We need two more triggers for the master, extending the automatic triggers created by the system for the cascading delete. In the master’s BEFORE DELETE, we “null out” the last detail row’s foreign key and “stamp” the TEMP_FK column. After the master row ’s deletion has been written, we go back and delete the detail row.CREATE TRIGGER BD_MASTER FOR MASTER
Let it be stressed that this example is unlikely to fit every requirement for mandatory relationships. There are usually several other factors to consider, in terms of both the business rules requirements and the programming interface. It is rare for trigger logic not to rise to the occasion.
Referential Integrity Support
Formal—or declarative—referential integrity (RI) constraints should be used wherever it is practicable to do so. The checking that occurs when formal referential integrity constraints are in force is all done with triggers, internally. If you want to extend the activity of the RI actions you have defined for a relationship, triggers are the way to do it.
The internal triggers in each phase fire after the custom ones you define yourself. Take care not to write triggers that conflict with what the declarative triggers have been assigned to do. If you find that your desired custom trigger action conflicts with the internal actions, a rethink will be needed of both your declarative RI definition and your custom trigger action.
Implementing RI Without Constraints
Some diehards who have been developing with Firebird and its InterBase cousins for years eschew declarative RI with a passion and use triggers to “roll their own.” There is no technical reason, with any Firebird version, to avoid declarative RI if you need RI—it works very well and it doesn’t eat much.
However, declarative RI requires a foreign key that, in turn, requires a mandatory index. Firebird does not yet have a way to enforce foreign keys without the mandatory index. There is a situation, common enough to warrant special attention, where the index on a foreign key may be so bad for the performance of queries involving the tables concerned that a formal referential relationship must be avoided. The phenomenon occurs when the design incorporates tables of the sort known as the “lookup” or “system” or “control” table.
The Lookup Table
A lookup or control table is typically a static table with a small row count, which may be used in a similar way in several different contexts. It consists of a small primary key and a description field, calculation factor, or some rule that processes need to refer to. Examples are tax tables, account types, transaction types, reason codes, and so on. It has been distilled out of the normalization process as a system table that is linked to by other tables, often many different ones, by storing the lookup key in the user table. Because one row in a lookup table supplies information to many rows, a slavish adherence to relational analysis rules often results in foreign keys being bestowed on the lookup key columns of the user tables.
It is a perfectly valid and standard way to use relations—what would we do without it? However, it tends to distribute a small range of possible lookup key values across a large, dynamic user table. Such large tables often carry a number of these lookup keys as foreign keys and with them a number of very unhealthy automatic indexes that cannot be dropped. The phenomenon of few values in a large index can give rise to indexes that become less and less selective as the table grows. Because of the nature of indexing in Firebird, these lookup indexes can kill query performance.
The indexes that support foreign keys are mandatory and can be dropped only by dropping the constraint. Yet, by dropping the constraint, you lose the protection of the automatic referential integrity triggers. The way out of this dilemma is to write your own referential integrity triggers.
Special Relationship: Custom RI Handling
This section addresses a particular kind of relationship, system lookups, that is not usually supported by declarative RI. The terms used here reflect the requirements of this case, since a fully customized RI setup is pointless for regular master-detail relationships. Figure illustrates the situation. A requestor, which can be any table, has a lookup key that points to a single, uniquely keyed row in a lookup table. The value in the row is provided by the lookup table on request.
To preserve referential integrity, we want triggers that will provide an eclectic set of safeguards for the users of the lookup table (the requestors), just as declarative RI provides safeguards to protect master-detail dependencies:
Implementing the Custom RI
Suppose we have these two tables:CREATE TABLE LOOKUP (
We’ll proceed to set up the existence rules for the two tables. We plan to use exceptions to stop DML events that would violate integrity, so we create them first:CREATE EXCEPTION NO_DELETE
The first trigger does the existence check when an attempt is made to delete a lookup row:SET TERM ^;
This one is the other side of the existence enforcement: a lookup key cannot be assigned if it does not exist in the lookup table:CREATE TRIGGER BA_REQUESTOR FOR REQUESTOR
We might now add further triggers to enforce other integrity rules we need. For example, this trigger will restrict any update or delete of the Lookup table to a specific user:CREATE TRIGGER BA_LOOKUP FOR LOOKUP
This trigger will check the input lookup code to make sure that it is the right one for the period of the transaction and correct it if necessary:CREATE TRIGGER BA_REQUESTOR1 FOR REQUESTOR
Updating Rows in the Same Table
Before considering using a trigger to update other rows in the same table, look carefully at the effect of setting off a cycle of nested activity. If a trigger performs an action that causes it to fire again, or it fires another trigger that performs an action that causes it to fire, an infinite loop results. For this reason, it is important to ensure that a trigger’s actions never cause the trigger to launch itself, even indirectly.
If you arrive at a point in your database design at which it is necessary to write a trigger to implement a data dependency between rows in the same table, it is likely to be a sign of poor normalization unless the dependency pertains to a tree structure. If a segment of the row structure affects, or is affected by, a change of state in another row, that segment should be normalized out to a separate table, with foreign keys to enforce the dependency rule.
Self-Referencing Tables and Trees
Self-referencing tables that implement tree structures are a special case. Each row in such a table is a node in a tree and inter -row dependencies are inherent. Any node potentially has two “lives”: one as a parent to nodes beneath it, the other as a child to a higher node. Triggers are likely to be required for all DML events, both to modify the behavior of referential integrity constraints and to maintain the metatables (graphs) used by some tree algorithms to make the state of the tree’s geometry available to queries. Triggers for trees should always be designed with conditions and branches that protect the structure from infinite loops.
Updating the Same Row
Never try to use an SQL statement to update or delete the same row that the trigger is operating on. The following, for example, is not advisable:CREATE TRIGGER O_SO_SILLY FOR ATABLE
Always use the NEW variables for same-row modifications and never resolve an exception by attempting to delete the row from within the trigger.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.