Managing Database Trigger Design

How to systematically check constraints in databases other than the standard constraints, e.g., (primary) keys and foreign keys? To solve this important problem in practice, one can use triggers, i.e., special database procedures that automatically start (‘fire’) upon attempts to modify data in a table. In practice, designing, writing, and maintaining triggers consistently is subtle, difficult, and error-prone, for many reasons: E.g., when multiple triggers are activated ‘at the same time’ or different trigger-types are mixed, the behaviour can become quite subtle or unpredictable, e.g., regarding their execution-order. Or when triggers invoke external actions, the triggering transaction might not be committed after all. Moreover, keeping triggers consistent when they must be changed is also very error-prone. So, it is very desirable to be able to (re)generate such triggers in a disciplined way. Therefore, our research question is how to manage the proper execution of checking constraints in database practice. To tackle these problems, we systematically assemble the triggers that should be activated ‘at the same time’ into one trigger. In that way, we keep control over what happens, and when. It keeps the trigger-order platform-independent as well. Using standard templates, we describe a generic way for generating triggers from a declarative specification. We intensively used and evaluated this approach in practice. Constraints between and within tables can be treated in this way. Using triggers, we also propose a straightforward implementation of SQL’s assertions. To develop triggers consistently, several design-decisions must be made. E.g., should the trigger stop checking after detecting the first violation or find and mention all violations? And what to do when there is a violation? E.g., a rollback or some ‘repair’, e.g., a cascading delete? We discuss and answer all these questions.