Triggers and Active Databases - PowerPoint PPT Presentation

About This Presentation

Triggers and Active Databases


Idea of triggers were developed for 'data constraints' Example: Relation of baseball players and salary. ... Brief History. 1975: Idea of 'integrity constraints' ... – PowerPoint PPT presentation

Number of Views:47
Avg rating:3.0/5.0
Slides: 32
Provided by: a15399
Learn more at:


Transcript and Presenter's Notes

Title: Triggers and Active Databases

Triggers and Active Databases
  • CS561

Practical Applications of Triggers and
Constraints Successes and Lingering Issues
Information in presentation based on VLDB2000
test-of-time paper
  • Stefano Ceri Roberta J. Cochrane
  • Politecnico di Milano IBM Almaden
    Research Center
  • Jennifer Widom
  • Stanford University

What are Triggers, and why ?
  • Idea of triggers were developed for data
  • 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

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

Uses / Advantages
  • To move application logic and business rules into
  • 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., ltif A then B holdsgt
  • 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

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

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

Example Constraints
  • Specify acceptable database states
  • ltcondition X must holdgt
  • Mapping
  • When ltpotentially invalidating operationsgt
  • If ltconstraint violatedgt
  • Then ltfix itgt

Example Constraints
  • Constraint Predicate not to hold on table
  • Example Every employees department must exist
  • Emp not exists (select from dept where dno
  • System produces in general
  • Create rule ltnamegt on table
  • When ltinvalidating opsgt
  • if exists (select from lttablegt where
  • Then ltactiongt
  • System produces for our example
  • Create rule ltnamegt on emp
  • When inserted, , updated (dno)
  • if exists (select from emp where not exists
    (select from dept where dnoemp.dno))
  • Then ltReject updategt
  • Similar rule created for dept table, and delete
    and updates on it.

Classification of Triggers
  • Two Types of Triggers
  • Generated based on some higher-level
  • 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

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

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
  • DBMS Services
  • Not generally used here

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
  • Need wizard to let developer specify trigger in
    higher level language

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

Challenge Semantics ?
  • What causes a rule to be triggered? (states, ops,
  • At what granularity are rules triggered ? (after
    tuple change, set level change, transaction,
  • What happens when multiples rules are triggered?
    (arbitrary order, numeric or priorities
  • 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
  • Query optimization also consider rules
  • Query execution interrelationship with
  • Indexing and rules
  • Transaction management and rules (coupled or not)

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

Challenge Rule Analysis
  • Termination produces a final state
  • Confluence terminates and produces a final
    state that does not depend on order of execution
  • Observable Determinism all visible actions
    performed by rules are the same at all states of
  • 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

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

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

Marking Example
  • Define rule FredJoe
  • On new to emp.sal
  • Where Fred
  • Then do replace emp (salnew.sal) where
  • 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 Freds

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 )

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

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

Transactions Rule Processing
  • 1. Event triggers set of rules R1 to Rn
  • 2. For each rule Ri in set, schedule transaction
  • 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
  • Recursive invocations yield trees of nested

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
Write a Comment
User Comments (0)