Triggers and Active Databases Alexandra Klenova Meghan Russ
Triggers and Active Databases Alexandra Klenova Meghan Russ Josh Sunshine Abe Weinograd
Timeline w 1: 40 -3: 05 Trigger History and Application n Josh and Abe w 3: 05 -3: 15 Break w 3: 15 -4: 00 Scalable Trigger Processing n Sasha and Meghan w 4: 00 -4: 30 Discussion
Practical Applications of Triggers and Constraints: Successes and Lingering Issues Stefano Ceri Politecnico di Milano Roberta J. Cochrane IBM Almaden Research Center Jennifer Widom Stanford University
References w S. Ceri, R. J. Cochrane, and J. Widom. Practical Applications of the Twenty-Sixth International Conference on Very Large Data Bases, pages 285 -296, Cairo, Egypt, September 2000. Invited Paper w U. Dayal, E. Hanson, and J. Widom. “Active Database Systems. In: Modern Database Systems: The Object Model, Iteroperability and Beyone, Addison-Wesley, Reading, Massachusetts, Sep 1994 w N. Paton, O. Diaz “Active Database Systems” w S. Ceri, J. Widom. “Deriving Production Rules for Constraint Maintenance” In Proceedings of the Six-teenth International Conference on Very Large Data Bases, pages 566 -577, Brisbane, Australia, August 1990
What are Triggers and where do they come from? w Idea of triggers were developed from data constraints w Triggers make a passive database active and allows it to react to situation n Example: Relation of baseball players and salary. If new player is added/old player is updated, trigger will check to see if team is over salary cap and will impose a penalty to team. w Event Condition Action rule n On event insert/update/delete, if condition C is true then do action
DEMO
Outline w How does this relate to Streaming Databases? w Issues and Advantages w Generated Triggers w Handcrafted Triggers w Further Classification
So How Does This Relate to Streams? w Triggers are the foundation for continuous queries n New data, or updated data can cause a reaction w Transforms old passive database, into an active one that waits on data in order to react to it.
STREAM and Triggers Similarities w Based on traditional DBMS w Extending DBMS to become Active w Uses continuous queries w Triggers are like continuous queries
STREAM and Trigger Differences w Triggers are simple but have no sense of order of evaluation w Triggers aren’t scalable w Many triggers cause confusion n Trigger cycle
Issues w Expressive Events n n Events that can be triggered are limited to insert, delete, and update No support for time actions l n Example: At 4 am copy essential tables and send alert to DBA No sophisticated trigger processor to handle complex triggers
Issues cont’d w Limitations n n Limit to prioritization and optimization when multiple triggers are opened Language is low-level and more difficult to deal with w Uniformity n Each DBMS has subtle differences in language w Subtle Behavior n n Triggers only execute after data is finished modifying External actions do not know if the trigger that executed it committed causing possible inconsistency.
Issues cont’d w Development Support n No support for handcrafted triggers, which result more generated triggers and trigger “wizards” w Performance n Increasing problems when there are many triggers on one table
Advantages w To move application logic and business rules into database n This allow much more functionality for DBAs to establish vital constraints and rules that apply to their applications
Types of Triggers w Two types of Triggers n n Generated: Handcrafted: usually specific to application w Three types of uses n n n Kernel DBMS: hard coded into kernel DBMS services: enhances database functionality External applications: creating triggers specific to app.
Generated Triggers w DBMS Kernel n Referential integrity l n If foreign key in a table is deleted or updated, it causes an action usually specified by user: set null/cascade Materialized View l Set of triggers that keep data consistent w Either re-computes view or changes view each time base data is changed
Generated Triggers cont’d w DBMS Services n Alerter l When data changes, message can be sent to user w Example: A sensor will notice that only one milk carton is left on the shelf in a market, and a message could be send to manager. n Replication l If a table is copied, a trigger will notice updates to table and will change copied table.
Handcrafted Triggers w External Applications n n Straightforward use of triggers Application Specific May compute derived columns (remember that this was a shortcoming of the relational model) l Use trigger wizard to let developer specify trigger in higher level language l
Further Classifications w Generated n n n n Constraint preserving Constraint restoring Invalidating Materializing Metadata Replication Extenders Alerters w Handcrafted n Ad hoc
What to Take Away w Triggers are foundation to Sreams w Triggers are limited in expression and performance w Two Types of Triggers: Generate and Handcrafted
- Slides: 20