Transcript Overview Invited Paper

Triggers and Active Databases
Alexandra Klenova
Meghan Russ
Josh Sunshine
Abe Weinograd
Timeline
 1:40-3:05 Trigger History and Application

Josh and Abe
 3:05-3:15 Break
 3:15-4:00 Scalable Trigger Processing

Sasha and Meghan
 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
 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
 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
 N. Paton, O. Diaz “Active Database Systems”
 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?
 Idea of triggers were developed from data constraints
 Triggers make a passive database active and allows it to
react to situation

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.
 Event Condition Action rule

On event insert/update/delete, if condition C is true then do
action
DEMO
Outline
 How does this relate to Streaming
Databases?
 Issues and Advantages
 Generated Triggers
 Handcrafted Triggers
 Further Classification
So How Does This Relate to
Streams?
 Triggers are the foundation for
continuous queries

New data, or updated data can cause a
reaction
 Transforms old passive database, into an
active one that waits on data in order to react
to it.
STREAM and Triggers
Similarities
 Based on traditional
DBMS
 Extending DBMS to
become Active
 Uses continuous
queries
 Triggers are like
continuous queries
STREAM and Trigger
Differences
 Triggers are simple but have no sense of
order of evaluation
 Triggers aren’t scalable
 Many triggers cause confusion

Trigger cycle
Issues
 Expressive Events


Events that can be triggered are limited to insert,
delete, and update
No support for time actions


Example: At 4am copy essential tables and send alert
to DBA
No sophisticated trigger processor to handle
complex triggers
Issues cont’d
 Limitations


Limit to prioritization and optimization when multiple
triggers are opened
Language is low-level and more difficult to deal with
 Uniformity

Each DBMS has subtle differences in language
 Subtle Behavior


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
 Development Support

No support for handcrafted triggers, which result
more generated triggers and trigger “wizards”
 Performance

Increasing problems when there are many
triggers on one table
Advantages
 To move application logic and business rules
into database

This allow much more functionality for DBAs to
establish vital constraints and rules that apply to
their applications
Types of Triggers
 Two types of Triggers


Generated:
Handcrafted: usually specific to application
 Three types of uses



Kernel DBMS: hard coded into kernel
DBMS services: enhances database functionality
External applications: creating triggers specific to app.
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 View

Set of triggers that keep data consistent
 Either re-computes view or changes view each time base data
is changed
Generated Triggers cont’d
 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 in a market, and a message could be send to
manager.

Replication

If a table is copied, a trigger will notice updates to table and will
change copied table.
Handcrafted Triggers
 External Applications


Straightforward use of triggers
Application Specific
May compute derived columns (remember that this
was a shortcoming of the relational model)
 Use trigger wizard to let developer specify trigger in
higher level language

Further Classifications
 Generated








Constraint preserving
Constraint restoring
Invalidating
Materializing
Metadata
Replication
Extenders
Alerters
 Handcrafted

Ad hoc
What to Take Away
 Triggers are foundation to Sreams
 Triggers are limited in expression and
performance
 Two Types of Triggers:Generate and
Handcrafted