Triggers and Active Databases

1
Triggers and Active Databases

• CS561

2
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

3
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

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

5
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

6
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
  transactions

7
Classification of Triggers

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

8
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

9
Example Constraints

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

10
Example Constraints

• Constraint Predicate not to hold on table
• Example Every employees department must exist
• Emp not exists (select from dept where dno
  emp.dno)
• System produces in general
• Create rule ltnamegt on table
• When ltinvalidating opsgt
• if exists (select from lttablegt where
  ltpredicategt)
• 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.

11
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

12
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

13
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
  services

14
Generated Triggers

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

15
Handcrafted Triggers

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

16
Handcrafted Triggers

• DBMS Services
• Not generally used here

17
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

18
Classification

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

• Handcrafted
• Application specific

19
Challenges
20
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 !
21
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)

22
Challenges Triggers in Products

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

23
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
  processing
• 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
  rollback

24
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

25
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

26
Marking Example

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

27
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 )

28
Transactions Coupling Modes

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

29
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

30
Transactions Rule Processing

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

31
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
  processing