Updating Other Tables - Firebird

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 (
ID INTEGER NOT NULL PRIMARY KEY,
DATA VARCHAR(10));
COMMIT;
CREATE TABLE DETAIL (
ID INTEGER NOT NULL PRIMARY KEY,
MASTER_ID INTEGER, /* The foreign key column is deliberately nullable */
DATA VARCHAR(10),
TEMP_FK INTEGER,
CONSTRAINT FK_MASTER FOREIGN KEY(MASTER_ID)
REFERENCES MASTER
ON DELETE CASCADE);
COMMIT;

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 is the only detail record: it can not be deleted.';
CREATE GENERATOR GEN_DETAIL;
COMMIT;

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 ^;
CREATE TRIGGER AI_MASTER FOR MASTER
ACTIVE AFTER INSERT OR UPDATE POSITION 1
AS
BEGIN
IF (NOT (EXISTS (
SELECT 1 FROM DETAIL WHERE MASTER_ID = NEW.ID
OR TEMP_FK = NEW.ID))) THEN
INSERT INTO DETAIL (MASTER_ID)
VALUES (NEW.ID);
ELSE
IF (NOT (EXISTS (
SELECT 1 FROM DETAIL WHERE MASTER_ID = NEW.ID))) THEN
UPDATE DETAIL SET
MASTER_ID = NEW.ID,
TEMP_FK = NULL
WHERE TEMP_FK = NEW.ID;
END ^

The detail table gets an automatically generated key:

CREATE TRIGGER BI_DETAIL FOR DETAIL
ACTIVE BEFORE INSERT AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_DETAIL, 1);
END ^

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
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
IF (NOT (EXISTS (
SELECT 1 FROM DETAIL
WHERE MASTER_ID = OLD.MASTER_ID
AND ID <> OLD.ID)))
THEN
EXCEPTION CANNOT_DEL_DETAIL;
END ^

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
ACTIVE BEFORE DELETE
AS
BEGIN
UPDATE DETAIL
SET MASTER_ID = NULL,
TEMP_FK = OLD.ID
WHERE MASTER_ID = OLD.ID;
END ^
/* */
CREATE TRIGGER AD_MASTER FOR MASTER
ACTIVE AFTER DELETE AS
BEGIN
DELETE FROM DETAIL
WHERE TEMP_FK = OLD.ID;
END ^
COMMIT ^
SET TERM ;^

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.

Lookup-Requestor relationship

Lookup-Requestor relationship

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:

  • The lookup row must not be deleted if a requestor is using it. For this, we need a BEFORE DELETE trigger on Lookup to check this and, if necessary, raise an exception and stop the action.
  • We should make and enforce a rule that requires the Requestor’s lookup key to be one that matches a key in Lookup. Our rule may or may not permit the lookup key to be null.
  • We may want to make a rule that the static value should never be changed. In a tax table, for example, the same (external) tax code may be associated with different rates and formulas from year to year. Perhaps the chief accountant may be permitted to change a lookup row.
  • A BEFORE UPDATE trigger on the Requestor would be required to handle a complex rule such as the one described in the previous point to check dates and possibly other criteria, in order to enforce the rule and pick the correct key.

Implementing the Custom RI

Suppose we have these two tables:

CREATE TABLE LOOKUP (
UQ_ID SMALLINT NOT NULL UNIQUE,
VALUE1 VARCHAR(30) NOT NULL,
VALUE2 CHAR(2) NOT NULL,
START_DATE DATE,
END_DATE DATE);
COMMIT;
/* */
CREATE TABLE REQUESTOR (
ID INTEGER NOT NULL PRIMARY KEY,
LOOKUP_ID SMALLINT,
DATA VARCHAR(20)
TRANSAC_DATE TIMESTAMP NOT NULL);
COMMIT;

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
'Can not delete row required by another table';
CREATE EXCEPTION NOT_VALID_LOOKUP
'Not a valid lookup key';
CREATE EXCEPTION NO_AUTHORITY
'You are not authorized to change this data';
COMMIT;

The first trigger does the existence check when an attempt is made to delete a lookup row:

SET TERM ^;
CREATE TRIGGER BD_LOOKUP FOR LOOKUP
ACTIVE BEFORE DELETE
AS
BEGIN
IF (EXISTS(
SELECT LOOKUP_ID FROM REQUESTOR
WHERE LOOKUP_ID = OLD.UQ_ID)) THEN
EXCEPTION NO_DELETE;
END ^

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
ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
IF (NEW.LOOKUP_ID IS NOT NULL
AND NOT EXISTS (
SELECT UQ_ID FROM LOOKUP
WHERE UQ_ID = NEW.LOOKUP_ID)) THEN
EXCEPTION NOT_VALID_LOOKUP;
END ^

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
ACTIVE BEFORE UPDATE OR DELETE
AS
BEGIN
IF (CURRENT_USER <> 'CHIEFACCT') THEN
EXCEPTION NO_AUTHORITY;
END ^

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
ACTIVE BEFORE INSERT OR UPDATE POSITION 1
AS
DECLARE VARIABLE LOOKUP_NUM SMALLINT;
DECLARE VARIABLE NEED_CHECK SMALLINT = 0;
BEGIN
IF (INSERTING AND NEW.LOOKUP_ID IS NOT NULL) THEN
NEED_CHECK = 1;
IF (UPDATING) THEN
IF (
(OLD.LOOKUP_ID IS NULL
AND NEW.LOOKUP_ID IS NOT NULL)
OR (OLD.LOOKUP_ID IS NOT NULL
AND NEW.LOOKUP_ID <> OLD.LOOKUP_ID)) THEN
NEED_CHECK = 1;
IF (NEED_CHECK = 1) THEN
BEGIN
SELECT L1.UQ_ID FROM LOOKUP L1
WHERE L1.START_DATE <= CAST(NEW.TRANSAC_DATE AS DATE)
AND L1.END_DATE >= CAST(NEW.TRANSAC_DATE AS DATE)
AND L1.VALUE2 = (SELECT L2.VALUE2 FROM LOOKUP L2
WHERE L2.UQ_ID = NEW.LOOKUP_ID)
INTO :LOOKUP_NUM;
NEW.LOOKUP_ID = LOOKUP_NUM;
END
END ^
COMMIT ^
SET TERM ;^

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
BEFORE UPDATE
AS
BEGIN
UPDATE ATABLE SET ACOLUMN = NEW.ACOLUMN
WHERE ID = NEW.ID;
END ^

Always use the NEW variables for same-row modifications and never resolve an exception by attempting to delete the row from within the trigger.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics