Oracle PL/SQL Best Practices

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.