Name
MOD-16: Populate columns of derived values with triggers.
Synopsis
Some applications require that extra information be stored in a record whenever it’s inserted, updated, or deleted. This information may or may not be supplied by the application itself.
Example
In this example, the date a record is updated is recorded within the record itself:
CREATE OR REPLACE TRIGGER set_updated_fields BEFORE UPDATE ON account_transaction FOR EACH ROW BEGIN IF :NEW.updated_date IS NULL THEN :NEW.updated_date := SYSDATE; END IF; END;
Benefits
You can guarantee that the fields will be populated because all records are processed by the triggers.
Challenges
If you have a set of standard columns whose values are set through triggers, those columns should not be provided values in application DML statements. It would probably make sense to build views on top of the base tables that hide the derived-value columns.
Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.