Triggers and Active Databases

1. Triggers and Active Databases CS561

2. Information in presentation based on VLDB’2000 “test-of-time” paper Practical Applications of Triggers and Constraints: Successes and Lingering Issues Stefano Ceri Roberta J. Cochrane Politecnico di Milano IBM Almaden Research Center Jennifer Widom Stanford University

3. What are Triggers, and why ? • Idea of triggers were developed for “data constraints” • Example: Relation of baseball players and salary. If new player added, trigger will check to see if team is over salary cap and will impose a penalty to team. • Triggers make a passive database “active” • Database reacts to certain situations • Event Condition Action rule : • on event insert/update/delete, • if condition C is true • then do action A

4. Brief History • 1975: Idea of “integrity constraints” • Mid 1980-1990: research in constraints & triggers • Languages and algorithms • SQL-92: constraints • Key constraints; referential integrity, domain constraints • Declarative spec and procedural interpretation • SQL-99: triggers/ECA (limited) • Early acceptance; Widely-varying support in products; “execution semantics” differ, how far to go ? • Now : • New “hot” incarnation in streaming …

5. Uses / Advantages • To move application logic and business rules into database • This allows more functionality for DBAs to establish vital constraints/rules of applications • Rules managed in some central “place” • Rules automatically enforced by DBMS, no matter which applications later come on line

6. Active DB vs Rule Systems • Expert Systems/AI: • Focus primarily on “reasoning” (in place of algorithmic solution), e.g., <if A then B holds> • Typically, no “active action” can be taken • Typically no notion of “triggering” based on “events”, but rather chaining of facts together • Typically little data (all in main memory) • Typically, limited performance (RETE network) • Typically no notion of concurrent users and transactions

7. Classification of Triggers • Two Types of Triggers • Generated: based on some higher-level specification • Handcrafted: usually specific to some application

8. Theme : Generated • Triggers (active rules) are difficult to write correctly • Idea: • Trigger application specified at higher level (declarative) • Actual triggers to implement the application generated from specification • Semi-automatic • Correctness guaranteed

9. Example : Constraints • Specify acceptable database states • <condition X must hold> • Mapping: • When <potentially invalidating operations> • If <constraint violated> • Then <fix it>

10. Example : Constraints • Constraint : Predicate not to hold on table • Example : Every employee’s department must exist • Emp: not exists (select * from dept where dno = emp.dno) • System produces in general: • Create rule <name> on table • When <invalidating ops> • if exists (select * from <table> where <predicate>) • Then <action> • System produces for our example: • Create rule <name> on emp • When inserted, , updated (dno) • if exists (select * from emp where not exists (select * from dept where dno=emp.dno)) • Then <Reject update> • Similar rule created for dept table, and delete and updates on it.

11. Classification of Triggers • Two Types of Triggers • Generated: based on some higher-level specification • Handcrafted: usually specific to some application • Three Classes of Usage • Kernel DBMS: hard coded into kernel • DBMS services: enhances database functionality • External applications: creating triggers specific to application

12. Generated Triggers • DBMS Kernel • Referential integrity • If foreign key in a table is deleted or updated, it causes an action usually specified by user: set null/cascade • Materialized views • Set of triggers that keep data consistent • Either re-computes view, or • Better changes view each time base data is changed

13. Generated Triggers • DBMS Services • Alerter • When data changes, message can be sent to user • Example: A sensor will notice that only one milk carton is left on the shelf, and a message could be send to manager. • Replication • If a table is copied, a trigger will observe updates to that original table and will change copied table. • Audit Trails, Migration, Extenders, etc. • Big Success Story : • Services simple to specify; yet procedural semantics. Only moderately configurable. • Example: IBM DB2 has numerous such trigger-based services

14. Generated Triggers • External Applications • Workflow management • External tools with support for generation of “Process Rules/Models”

15. Handcrafted Triggers • Embedded DBMS Kernel • Metadata management • Internal audit trails • But: • Triggers excellent for prototyping; but often replaced by code directly

16. Handcrafted Triggers • DBMS Services • Not generally used here

17. Handcrafted Triggers • External Applications • Straightforward use of triggers • Application specific • Additional forms of “data integrity” • Could be used to compute derived columns • Or, enforce arbitrarily complex application-specific semantics • Examples: • Business rules, supply chain management, web applications, etc. • But : • Triggers are challenging to use for complex applications • Need wizard to let developer specify trigger in higher level language

18. Generated Constraint preserving Invalidating Materializing Metadata Replication Extenders Alerters Handcrafted Application specific Classification

19. Challenges

20. Challenge : Semantics ? • What causes a rule to be triggered? (states, ops, transitions) • At what granularity are rules triggered ? (after tuple change, set level change, transaction, etc). • What happens when multiples rules are triggered? (arbitrary order, numeric or priorities suggested) • Can rules trigger each other, or themselves? In general, many subtle design choices exist !

21. Support for Triggers in DBMS? • DDL : Add/Disable triggers; scope • DML : What can rule do • Extra support for rule execution: e.g., “delta tables” • Query optimization : also consider rules • Query execution : interrelationship with constraints • Indexing and rules • Transaction management and rules (coupled or not)

22. Challenges – Triggers in Products • No uniformity among trigger support in products • Triggers simple : typically cannot encode complex conditions (optimization problem; poor performance) • Typically, no time-based events • Semantics: transactional; interrelationships; etc. • Triggers aren’t scalable (one per table often, or so) • Triggers difficult to use (if many of them) • Trigger interaction analysis • No high-quality trigger design tools

23. Challenge: Rule Analysis • Termination: produces a final state • Confluence : terminates and produces a final state that does not depend on order of execution • ObservableDeterminism : all visible actions performed by rules are the same at all states of processing • Termination : • Find cycles • Examine rules in cycle for behavior • Could determine that terminate in some cases • Data dependent : even if at compile-time has cycle, still may be useful • In general , undecidable ( ~ FOL with predicates and implication) • In practice (Oracle) : • Optimistic solution • Terminate after 25 trigger invocations, and rollback

24. Trigger Implementation • Two methods (Postgres): • Tuple-level marking • Query rewriting • Pros and Cons : • Tuple-level marking – works well for lots of rules applying to few tuples • Query rewriting – works well for few rules applying to lots of tuples • But: • Semantics may differ

25. Implementation : Marking • Place markers on all tuples for which rules apply : • If markers encountered during execution • Call rule processor • Note: • Markers must be maintained through modifications • Place stubs on tables with potential markers

26. Marking Example Define rule FredJoe On new to emp.sal Where emp.name = “Fred” Then do replace emp (sal=new.sal) where emp.name = “Joe” • Pace marker : • on emp tuples with name = “Fred” • Maintain marker : • if name modified, then marker is dropped • Place stub • on emp table to catch new “Fred’s”

27. Query Rewriting : Example • Inset modules between parser and query optimizer • Query + Rules  Set of Queries Example: On replace to emp.sal Then do append to audit (cur.sal, new.sal) + Replace emp (sal = sal * 1.1 )  append to audit (emp.sal, 1.1 * emp.sal) replace emp (sal = sal * 1.1 )

28. Transactions – Coupling Modes • Specify transaction relationship of when rules execute relative to triggering user actions • Result: Nested Transaction Model • Fore-runner : HIPAC at HP

29. Transactions – Coupling Modes • Specify transaction relationship between: • Event and Condition : E-C Coupling • Condition and Action : C-A Coupling • Coupling modes: • Immediate • Deferred : at end of transaction • Separate : run as separate transaction

30. Transactions – Rule Processing 1. Event triggers set of rules R1 to Rn 2. For each rule Ri in set, schedule transaction to: • Evaluate condition • If true, schedule transaction to execute action • Processing of transaction scheduling based on coupling modes • Notes: • Rule-generated operations in 2(a) and (b) may recursively invoke 1/2 • Multiple triggered rules yield concurrent transactions • Recursive invocations yield trees of nested transactions

31. Conclusion on Triggers + DBMS • A huge area with challenging Issues, both at semantics and performance level • Potential for many practical usages • Products have only incorporated a small subset of features; there is an SQL standard (SQL99), and yet there is not 100% uniformity • Application of theories and techniques are and will have impact beyond static databases and rule processing