The fourth use-case for triggers

In our previous post we talked about three of the four use-cases we introduced. Triggers can:

1. Assign/modify (row) column values.
2. Execute insert/update/delete statements.
3. Execute non-transactional stuff.

We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.

In this post we continue with use-case four. Triggers can:

4. Execute select statements.

This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a reason for this: a very important database-concept requires you to query other data from within triggers.

This concept is called: data integrity constraints. And it's exactly this use-case where, in my opinion, triggers form the means to an end.

Now beware though, and we give you this warning beforehand: using triggers to implement data integrity constraints, is by far not easy. In fact it's extremely complex (as we will detail in future posts). But to me that is no reason to not use triggers. And this use-case will not suffer from the automagic side-effects the other three use-cases had. So I disagree here when Tom Kyte says that even for this use-case 'thou shall not use triggers'.

Recall the 'business rule' that we were trying to implement using triggers: a department cannot employ a manager without a clerk in the same department. That rule is in fact a data integrity constraint. Now, few people know of the documented feature in the SQL standard called: assertions. SQL assertions have been part of the SQL standard since 1992. See this link for the syntax/grammar (it's the eighth 'key sql statement' from the top of the list). Had database vendors supplied us with support for the two-decades old SQL assertion feature, then we could have straightforwardly implemented the data integrity constraint with one CREATE ASSERTION statement:

Translating the above assertion into somewhat awkward english, it goes like this: there cannot exist a department such that this department employs a manager, and such that this department does not employ a clerk.

Once the assertion is created, it's up to the DBMS to enforce the integrity constraint in a correct and (hopefully) efficient manner. Just like you expect the DBMS to correctly and efficiently enforce a uniqueness constraint, or a foreign key. Conceptually these constraints are no different than assertions: it's just that these two represent (constraint) patterns that occur so frequently in every database design, that we've been provided with dedicated SQL language constructs to declare them. But theoretically the UNIQUE / PRIMARY KEY and FOREIGN KEY language constructs are redundant, when assertions are available: both can be rewritten using a CREATE ASSERTION statement.

Would you use assertions, if Oracle provided them? I certainly would, and you would probably too. You are using the other declarative means to implement data integrity (check constraints, primary key, foreign key) right now too, aren't you?

Implementing data integrity constraints in a declarative manner, enables a kind of separation of concerns: you implement your integrity constraints once-and-forall while you create your tables. And then you build business logic on top of the database design without having to worry about validating integrity constraints: all you need to cater for is error-handling, in case your business logic tries to modify/store data in such a way that it violates one or more of the declared integrity constraints. If you want to read up on this way of implementing data-centric applications theHelsinkiDeclaration.blogspot.com is a good starting point.

By the way, there's a reason why DBMS vendors have yet to supply us with support for the CREATE ASSERTION statement:

Developing a correct and efficient implementation for an arbitrary complex assertion (which is what we're asking for), is very hard. By 'correct' we mean, the implementation must properly deal with concurrent transactions manipulating data that is involved in the assertion. By 'efficient' we mean, the implementation must not just run the SQL-predicate that was asserted in the create assertion command, but it must be able to a) detect when a transaction might potentially violate the assertion, and then b) run a minimal check to ensure the continued validity of the assertion.

As far as I know the problem areas described above, haven't been fully researched yet by the scientific community: for one I'm unable to find this research, and had it been researched fully, it would have been fairly easy for a database vendor like Oracle to 'stand on the shoulders' of those researchers and provide us support for create assertion.

So there we are: the fourth use-case for triggers being implementing data integrity constraints.

To be continued...