In our previous post we talked about three of the four use-cases we introduced. Triggers can:
In this post we continue with use-case four. Triggers can:
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...