Custom Action Triggers - Firebird

It is perfectly possible to write your own action triggers to customize or extend referential behavior. Although the automatic triggers are flexible enough to cover most requirements, there is one special case where custom triggers are generally called for. This is the case where creation of the mandatory enforcing index on the foreign key column is undesirable because the index would be of very low selectivity.

Broadly, indexes of low selectivity occur where a small number of possible values is spread over a large table, or where only a few possible values are ever used in the actual table data. The resulting massive duplication of values in the index—described as long chains —can impact query performance severely as the table grows.

When writing custom referential triggers, you must make sure that your own triggers or your application code will preserve referential integrity when data in any key changes. Triggers are much safer than application code, since they centralize the data integrity rules in the database and enforce them for all types of access to the data, be it by program, utility tool, script, or server application layer.

Without formal cascading update and delete actions, your custom solution must take care of rows in child tables that will be affected by changes to or deletions of parent keys. For example, if a row is to be deleted from the referenced table, your solution must first delete all rows in all tables that refer to it through foreign keys.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics