Practical Applications of Constraints and Triggers: Successes and Lingering Issues

Abstract

From about the mid-1980's to the mid-1990's there was a flurry of research activity in the area of database triggers and constraints, seeing the development of numerous research proposals and prototypes. Soon thereafter, most mainstream database products ramped up their support for constraints and triggers, with expressive constraint specifications appearing in the SQL-92 standard, and both constraints and triggers in the SQL-99 standard. We briefly review the emergence of research in constraints and triggers, and we briefly describe standards and current commercial support. We then focus on practical applications of triggers. We describe a variety of interesting and significant ways in which triggers have been put into practice, and we classify trigger applications along two dimensions: "handcrafted" versus "generated", and "kernel DBMS" versus "DBMS services" versus "external applications". We also argue that a significant portion of these trigger applications are in fact nothing more than "constraint-maintainers" for various classes of integrity constraints, indicating that our work a decade ago -- if not itself put into practice directly -- was not far off the mark. Finally, we analyze the evolution of trigger applications and discuss some lingering shortcomings in database constraint and trigger systems.