Transcript Overview of Triggers+DB : Opportunities and Issues

Triggers and Active
Databases
CS561
Information in presentation based on VLDB’2000 “test-of-time” paper
Practical Applications of
Triggers and Constraints:
Successes and Lingering Issues
Stefano Ceri
Politecnico di Milano
Center
Roberta J. Cochrane
IBM Almaden Research
Jennifer Widom
Stanford University
What are Triggers, and why ?

Idea of triggers were developed for “data constraints”


Triggers make a passive database “active”


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.
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
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 …
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., <if A then B
holds>
 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
Classification of Triggers
 Two
Types of Triggers
 Generated:
based on some higher-level
specification
 Handcrafted: usually specific to some
application
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
 <condition
X must hold>
 Mapping:
 When
<potentially invalidating operations>
 If <constraint violated>
 Then <fix it>
Example : Constraints


Constraint : Predicate not to hold on table
Example : Every employee’s department must exist


Emp: not exists (select * from dept where dno = emp.dno)
System produces in general:



Create rule <name> on table
When <invalidating ops>
if exists (select * from <table> where <predicate>)
 Then <action>

System produces for our example:



Create rule <name> on emp
When inserted, , updated (dno)
if exists (select * from emp where not exists (select * from dept where
dno=emp.dno))
 Then <Reject update>

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 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

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
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




Examples:


Additional forms of “data integrity”
Could be used to compute derived columns
Or, enforce arbitrarily complex application-specific semantics
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
Challenges
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)
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 aren’t scalable (one per table often, or
so)
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 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
Trigger Implementation

Two methods (Postgres):
 Tuple-level marking
 Query rewriting

Pros and Cons :
marking – works well for lots of rules
applying to few tuples
 Query rewriting – works well for few rules applying to
lots of tuples
 Tuple-level

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 emp.name = “Fred”
Then do replace emp (sal=new.sal) where emp.name = “Joe”

Pace marker :
 on

Maintain marker :


emp tuples with name = “Fred”
if name modified, then marker is dropped
Place stub
 on
emp table to catch new “Fred’s”
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 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
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