scalable trigger processing n.
Skip this Video
Loading SlideShow in 5 Seconds..
Scalable Trigger Processing PowerPoint Presentation
Download Presentation
Scalable Trigger Processing

Loading in 2 Seconds...

play fullscreen
1 / 35

Scalable Trigger Processing - PowerPoint PPT Presentation

  • Uploaded on

Scalable Trigger Processing. Discussion of publication by Eric N. Hanson et al Int Conf Data Engineering 1999 CS561. Motivation. Triggers popular for: Integrity constraint checking Alerting, logging, etc. Commercial database systems Limited triggering capabilities

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Scalable Trigger Processing

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
scalable trigger processing

Scalable Trigger Processing

Discussion of publication by

Eric N. Hanson et al

Int Conf Data Engineering 1999


  • Triggers popular for:
    • Integrity constraint checking
    • Alerting, logging, etc.
  • Commercial database systems
    • Limited triggering capabilities
    • 1 trigger/update-type on table; or at best 100.
  • But : Current technology doesn’t scale well
  • And, internet and web-based applications may need millions of triggers.
an example trigger
An Example Trigger
  • Example “stock ticker notification”:
    • Stock holding: 100*IBM
    • Query: Inform an agent whenever the price of the stock holding crosses $10,000

Create Trigger stock-watch

from quotes q

on update(q.price)

when‘IBM’ and 100*q.price > 10,000

do raise event ThresholdCrossed(100*q.price).

    • Note: We may need 1,000 or millions of such triggers
    • Web interface may allow users to create such triggers
what next
What Next?
  • Problem description
  • TriggerMan system architecture
  • Predicate index
  • Trigger processing
problem definition
Problem Definition
  • Given: Relational DB, Trigger statements, Data Stream
  • Find: Triggers corresponding to each stream item
  • Objective: Scalable trigger processing system
  • Assumptions:
    • Number of distinct structures of trigger expressions is relatively small
    • All trigger expression structures small enough to fit in main memory
the problem once more
The Problem, once more.
  • Requires millions of triggers (on huge data).
  • Steps for trigger processing
    • Event monitoring
    • Condition evaluation
    • Executing triggered action
  • Response time for database operations critical !
related work
Related Work

ECA Model

(not scalable)


Parallel Processing




(smaller rule set)

Range Predicates, Marking-Based

[Hans96b, Ston90]

(large memory, complicated storage)

overall driving idea
Overall Driving Idea
  • If large number of triggers are created, then many have the same format.
  • Triggers share same expression signature except that parameters substituted.
  • Group predicates from trigger conditions based on expression signatures into equivalence classes
  • Store them in efficient main memory data structures
  • TriggerMan Datablade (lives inside Informix)
  • Data Sources
    • Local/remote tables/streams; must capture updates and transmit to TriggerMan (place in a queue)
  • TriggerMan Client applications
    • Create /drop triggers, etc.
  • TriggerMan Driver
    • Periodically involve TmanTest() fn to perform condition testing and action execution.
  • TriggerMan console
    • Direct user interaction interface for trigger creation, system shutdown, etc.
triggerman syntax
TriggerMan Syntax
  • Trigger syntax

create trigger <triggerName> [in setName]


from fromList

[on eventSpec]

[when condition]

[group by attributeList]

[having groupCondition]

do action

example salary increases
Example : Salary Increases

Update Fred’s salary when Bob’s salary is updated

create trigger updateFred

from emp

on update (emp.salary)

when = ’Bob’

do execSQL ’update emp set salary=:NEW.emp.salary where’’Fred’’’

example real estate database
Example : Real Estate Database

“If new house added which is in neighborhood that salesperson Iris reprensents then notify her”

House (hno,address,price,nno,spno)

Salesperson (spno,name,phone)

Represents (spno,nno)

Neighborhood (nno,name,location)

create trigger IrisHouseAlert

on insert to house

from salesperson s, house h, represents r

when = ‘Iris’ and s.spno=r.spno and r.nno=h.nno

do raise event NewHouseInIrisNeighborhood(h.hno, h.address)

trigger condition structure
Trigger Condition Structure
  • Expression signature
  • Expression signature consists of
    • Data source ID
    • Operation code, e.g. insert, delete, etc.
    • Generalized Expression (parameterized)

FROM: Data src: emp

ON: Event : update

WHEN: boolean exp.



condition structure contd
Condition structure (contd)
  • Steps to obtain canonical representation of WHEN clause
    • Translate expression to CNF
    • Group each conjunct by data source they refer to
  • Selection Predicate will be of form :

(C11 OR C12 OR ..) AND ... AND (Ck1 OR …),

where each Cij refers to same tuple variable.

  • Each conjunct refers to zero, one, or more data sources
  • Group conjuncts by set of sources they refer to
    • If one data source, then selection predicate
    • If two data sources, then JOIN predicate
triggers for stock ticker notification
Triggers for stock ticker notification
  • Create triggerT1fromstock

whenstock.ticker = ‘GOOG’ and stock.value < 500


  • Create triggerT2fromstock

whenstock.ticker = ‘MSFT’ and stock.value < 30


  • Create triggerT3fromstock

whenstock.ticker = ‘ORCL’ and stock.value < 20


  • Create triggerT4 from stock

whenstock.ticker = ‘GOOG’


expression signature
Expression Signature
  • Idea: Common structures in condition of triggers

Expression Signature:

      • E1: stock.ticker = const1 and stock.value < const2
    • Expression Signature:
      • E2: stock.ticker = const3
  • Expression signature defines equivalence class of all instantiations of expression with different constants

T1: stock.ticker = ‘GOOG’ and stock.value < 500

T2: stock.ticker = ‘MSFT’ and stock.value < 30

T3: stock.ticker = ‘ORCL’ and stock.value < 20

T4: stock.ticker = ‘GOOG’

what to do now
What to do now
  • Only a few distinct expression signatures, build data structures to represent them explicitly (in memory)
  • Create constant tables that store all different constants, and link them to their expression signature
main structures
Main Structures
  • A-treat Network
    • Network for trigger condition testing
      • For a trigger to fire, all conditions must be true
  • Expression Signature
    • Common structure in a trigger
      • E1: stock.ticker = const1 and stock.value < const2
  • Constant Tables
    • Constants for each expression signature
a treat network to represent a trigger


stock.value < const2

stock.ticker = const1


Node 1

Node 2



A-Treat Network to represent a trigger
  • For each trigger condition
  • stock.ticker = const1 and stock.value < const2
condition testing
Condition Testing
  • A-Treat network is a discrimination network for trigger condition testing.
  • For a predicate to be satisfied, all its conjuncts should be true.
  • This is checked using A-Treat network.
a treat network hanson 1992
A-Treat network (Hanson 1992)

Define rule SalesClerk

If emp.sal>30,000

And emp.dno=dept.dno


And emp.jno=job.jno

And job.title=“clerk”

Then Action

expression signature table
Expression Signature Table

E1: stock.ticker = const1 and stock.value < const2

E2: stock.ticker = const3

constant tables
Constant Tables
  • Tables of constants in trigger conditions



T1: stock.ticker = ‘GOOG’ and stock.value < 500

T2: stock.ticker = ‘MSFT’ and stock.value < 30

T3: stock.ticker = ‘ORCL’ and stock.value < 20

T4: stock.ticker = ‘GOOG’

  • Primary tables
    • trigger_set (tsID, name, comments, creation_date, isEnabled)
    • Trigger (triggerID, tsID, name, comments, trigger_text, creation_date, isEnabled, …)
    • Trigger cache in main memory for recently accessed triggers.
predicate index
Predicate Index
  • Tables
    • expression_signature(sigID, dataSrcID, signatureDesc, constTableName, constantSetSize, constantSetOrganization)
    • const_tableN(exprID, triggerID, nextNetworkNode, const1, … constK, restOfPredicate)
  • Root of predicate index linked to data source predicate indices
  • Each data source contains an expression signature list
  • Each expression signature links to its constant table.
  • Index expressions on most selective conjunct (rest on fly).
predicate index1
Predicate Index


Goal: Given an update, identify all predicates that match it.

processing trigger definition
Processing Trigger Definition
  • Parse the trigger and validate it
  • Convert the when clause to conjunctive normal form
  • Group the conjuncts by the distinct sets of tuple variables they refer to
  • Form a trigger condition graph, that is, undirected graph with node for each tuple variable and edge for join predicates.
  • Build A-Treat network
processing trigger definition 2
Processing trigger definition (2)
  • For each selection predicate
    • If predicate with same signature not seen before
      • Add signature of predicate to list
      • And, add signature to expression_signature table
      • If signature has a constant placeholder in it, create a constant table for the signature.
      • Add constants
    • Else if predicate has constants, add a row to the constant table for the expression
alternate organizations
Alternate Organizations
  • Storage for the expression signature’s equivalence class:
    • Main memory lists
    • Main memory index
    • Non-indexed database table
    • Indexed database table
  • For each expression signature, choose a structure depending on number of triggers.



processing update descriptors
Processing update descriptors
  • On getting an update descriptor (token)

(data src ID, operator code, old/new tuple)

    • Locate data source predicate index from root of predicate index.
    • For each expression signature, find constant matching the token using index.
    • Check additional predicate clauses against the token.
    • When all predicate clauses of a trigger have matched, pin the trigger in main memory
    • Bring in A-treat network representing that trigger to process aremaining part of trigger, like join, etc.
    • If trigger condition is satisfied, execute action.
processing an update
Processing an Update

Update Stock (ticker=GOOG, value=495)


Index of


Other source

Predicate index…



E1: stock.ticker = const1 and stock.value < const2




  • Better scalability even on single processor
  • Identified elements that can be parallelized
    • Token-level
      • Multiple tokens processed in parallel
    • Condition-level
      • Multiple selection conditions tested concurrently
    • Rule-action-level
      • Multiple rule actions fired at the same time
    • Data-level
      • Set of data values in the network processed in parallel
conclusion overall key points
Conclusion : Overall Key Points
  • If a large number of triggers are created, many of them have almost the same format
  • Group triggers with same structure together into expression signature equivalence classes
  • Number of distinct signatures is small enough to fit into main memory (index)
  • Develop a selection predicate index structures
  • Architecture to build a scalable trigger system.