Supplementing Data Integrity Using Triggers

Data integrity that cannot be controlled declaratively is most often implemented using database triggers. Examples include row-wise validations, access-control mechanisms, and preserving data across parent-child relationships. Special cases of maintaining consistent data parent-child tables are those of DELETE CASCADE and UPDATE CASCADE. DELETE CASCADE refers to automatic deletion of child rows when a parent row is deleted and can be specified declaratively using foreign keys. UPDATE CASCADE functionality refers to the automatic update of the primary key in a child table when the corresponding primary key in a parent table is updated. Pre-Oracle 8i, this posed a challenge to PL/SQL developers with the mutating table effect preventing the child row from being updated after updating the parent row in case the parent and child rows were linked by a foreign key (that is most often the case).

As the mutating table error due to constraining table effect no longer occurs as of Oracle 8i, an AFTER UPDATE row-level trigger can be defined on the parent table to implement update cascade functionality. This section highlights the implementation of an UPDATE CASCADE functionality to preserve data integrity between parent and child tables. This eliminates the need to delegate the same task to the front-end application or to have to control it otherwise.