Implementing change event rules

Previous post in this series: implementing dynamic data constraints

The CDM classification scheme has divided the change event rules into two categories: change event rules with DML and without DML. Since I have the same advice for both of these categories I am elaborating on them both in the same post.






Simple default value rules

These are implemented declaratively by specifying a default value clause for a column. When you don't specify the column during insert, the default value will be assigned. Note that the default value will not be assigned when you specify the value explicitly as null during insert.

rwijk@ORA11GR1> create table t
  2  ( id     number(9)
  3  , name   varchar2(10)
  4  , status varchar2(3) default 'NEW'
  5  )
  6  /

Table created.

rwijk@ORA11GR1> insert into t (id,name) values (1, 'ALAN')
  2  /

1 row created.

rwijk@ORA11GR1> insert into t (id,name,status) values (2, 'BOB', 'OLD')
  2  /

1 row created.

rwijk@ORA11GR1> insert into t (id,name,status) values (3, 'CHARLES', null)
  2  /

1 row created.

rwijk@ORA11GR1> select * from t
  2  /

        ID NAME       STA
---------- ---------- ---
         1 ALAN       NEW
         2 BOB        OLD
         3 CHARLES

3 rows selected.

Complex default value rules, Other change event rules and change event rules without DML

These change event rules are typically worded as "If A happens, always do B". The categories differ in what B is: Is it a complex default value, regular DML, or something outside the database.

In order to never forget that B should happen, a database trigger may sound tempting. But it is like smoking. One cigaret might make you look cool and will not harm your body much, but do it consistently and you are coughing all day and your lungs are black inside. You will end up in a situation where nobody understands all ramifications when you put that enddate in your contracts table. Maintenance costs will rise, because developers constantly get confused about unexpected code behaviour. And eventually the system will have to be replaced because it is just too complex and expensive to get anything done.

And then you have the performance considerations, because everything gets done row by row. And for the change event rules without DML, there is an extra trap as well when doing non-DML stuff that cannot be rollbacked. You'll be sending that email when in fact the update was rollbacked and just never took place. Or because of write consistency you might end up sending that email twice.

So the advice here is simple: don't ever implement these kind of rules using database triggers.

Code them in your api's. In the same way as I explained the api approach in the earlier blogpost about Implementing entity rules. Imagine to just read your end_contract procedure and seeing a series of procedure calls with clear names. And I've said it before in the database triggers are evil post: you make sure nobody bypasses the api by not granting table privileges to anyone, revoking the create session privilege after each install and patch in the owner schema. Only execute privileges on your api are granted.