Triggers and Active Databases

1
Triggers and Active Databases

• Alexandra Klenova
• Meghan Russ
• Josh Sunshine
• Abe Weinograd

2
Timeline

• 140-305 Trigger History and Application
• Josh and Abe
• 305-315 Break
• 315-400 Scalable Trigger Processing
• Sasha and Meghan
• 400-430 Discussion

3
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

4
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

5
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

6
DEMO
7
Outline

• How does this relate to Streaming Databases?
• Issues and Advantages
• Generated Triggers
• Handcrafted Triggers
• Further Classification

8
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.

9
STREAM and TriggersSimilarities

• Based on traditional DBMS
• Uses continuous queries

• Extending DBMS to become Active
• Triggers are like continuous queries

10
STREAM and TriggerDifferences

• Triggers are simple but have no sense of order of
  evaluation
• Triggers arent scalable
• Many triggers cause confusion
• Trigger cycle

11
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

12
Issues contd

• 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.

13
Issues contd

• 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

14
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

15
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.

16
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

17
Generated Triggers contd

• 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.

18
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

19
Further Classifications

• Generated
• Constraint preserving
• Constraint restoring
• Invalidating
• Materializing
• Metadata
• Replication
• Extenders
• Alerters

• Handcrafted
• Ad hoc

20
What to Take Away

• Triggers are foundation to Sreams
• Triggers are limited in expression and
  performance
• Two Types of TriggersGenerate and Handcrafted