Implementing dynamic data constraints

Previous post: Implementing inter-entity rules



Create rules

These rules either originate from a decomposed inter-entity rule or they were invented on their own. Again you have two choices of implementing them: using database triggers or incorporating in your api's. And again it's a matter of whether you are willing to lose performance or not. You'll probably want to be consistent throughout all business rule implementations. Stick with a database trigger approach or with an api approach. On the plus side for database triggers this time, some create rules can be validated using a before statement insert trigger instead of for each row. This means you won't have to pay a performance penalty for the occasional rule like "You may not create a project assignment when the OUT_OF_BUDGET system variable is set." Although most create rules require querying some related record and will suffer the row-by-row processing performance penalty. When using an api approach, you'll again code all create rules to be able to handle a set of records for maximum performance.


Attribute transition rules

This is a category where it is actually convenient to use a database trigger. It's because of the easy access to the :old and :new values in a row level trigger. The example "Allowed transitions for civil state of an employee are Unmarried -> Married -> Divorced -> Married." can be implemented like this:

rwijk@ORA11G> create table employees
  2  ( name varchar2(10) primary key
  3  , civil_state varchar2(1) check (civil_state in ('U','M','D'))
  4  )
  5  /

Table created.

rwijk@ORA11G> insert into employees
  2  select 'ALAN', 'U' from dual union all
  3  select 'BOB', 'M' from dual union all
  4  select 'CLARK', 'D' from dual
  5  /

3 rows created.

rwijk@ORA11G> create trigger employees_bru
  2  before update of civil_state on employees for each row
  3  begin
  4    if :old.civil_state || :new.civil_state not in ('UM','MD','DM')
  5    then
  6      raise_application_error(-20000,'Illegal transition of civil state');
  7    end if;
  8  end;
  9  /

Trigger created.

rwijk@ORA11G> update employees
  2     set civil_state = 'D'
  3   where name = 'BOB'
  4  /

1 row updated.

rwijk@ORA11G> update employees
  2     set civil_state = 'U'
  3   where name = 'BOB'
  4  /
update employees
       *
ERROR at line 1:
ORA-20000: Illegal transition of civil state
ORA-06512: at "RWIJK.EMPLOYEES_BRU", line 4
ORA-04088: error during execution of trigger 'RWIJK.EMPLOYEES_BRU'

Using the api approach you will have api-procedures for "getting_married" or "getting_a_divorce" where the check will be in the where clause. An error message can then be raised when 0 rows are updated (if sql%rowcount = 0 then raise_application_error...).


Transferable relationship rules

This one is easy using an api approach: don't offer an api-procedure that transfers a relationship. Using a database trigger approach is also quite simple: "if updating('<fk-column>') then raise_application_error(...); end if;"


Other update rules, modify rules and other delete rules

The same holds true for these rules as described in the section "Create rules".


Relationship rules

Relationship rules were also mentioned as a static data constraint, and now they appear again as a dynamic data constraint. Strictly speaking it is right to split them into two categories, although it is a little fancy. The dynamic relationship rules are implemented by the keywords ON DELETE CASCADE and ON DELETE SET NULL in the foreign key constraint and by the foreign key constraint itself. The example "If a department that still has employees is deleted, give an error" is what happens if you define a regular foreign key constraint. The ON DELETE CASCADE will automatically delete child records when the master is deleted, instead of erroring out. And the ON DELETE SET NULL will automatically orphan child records when the master is deleted.

Next post: Implementing change event rules