Triggers and Active Databases - Advanced Database System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Triggers and Active Databases - Advanced Database System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Triggers and Active Databases

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

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

Active DB vs Rule Systems

• Expert Systems/AI:
  • Focus primarily on “reasoning” (in place of algorithmic solution), e.g.,
  • 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

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

Example : Constraints

• Specify acceptable database states

• Mapping:

• When
• If
• Then

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

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

Generated Triggers

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

Handcrafted Triggers

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

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

Classification

• Generated
  • Constraint preserving
  • Invalidating
  • Materializing
  • Metadata
  • Replication
  • Extenders
  • Alerters
    • Handcrafted
      • Application specific

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!

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)