Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Constraints and Triggers, Triggers Intro

“Event-Condition-Action-Rules”
When event occurs, check condition; if true, do action
1) Move monitoring logic from apps into DBMS
2) enforce constraints
– beyond what constraint system supports
– automatic constraint repair

* implementations vary significantly, Intro SQL Standard; Demo, SQLite

Triggers in SQL
Create Trigger name
Before|After|Instead of events (insert (new), delete(old) or update(old, new))
[ referencing-variables ] – reference modified data (old row, new row, old table, new table)
[ For Each Row ] – once for each modified tuple
When ( condition ) – like SQL where, general assertion
action – SQL statement

Example:
Referential Integrity: R.A references S.B, cascaded delete
Create Trigger Cascade
After Delete On S
Referencing Old Row as O
For Each Row
[ no condition ] Delete From R Where A = O.B
or
Create Trigger Cascade
After Delete On S
Referencing Old Table as OT
[ For Each Row ] [ no condition ] Delete From R Where A in (select B from OT)

Tricky Issues
– Row-level vs. statement-level
– new/old row and new/old table
– Before, Instead of
– multiple triggers activated at the same time – which one goes first?
– trigger actions activating other triggers (chaining)
– also self-triggering, cycles, nested invocations
– conditions in When vs. as part of action
* implementations vary significantly

Example:
T(K, V) – K key, V value
Create Trigger IncreaseInserts
After Insert On T
Referencing New Row as NR, New Table as NT
For Each Row
When (Select Avg(V) From T) < (Select Avg(V) From NT) Update T set V=V+10 where K=NR.K No statement-level equivalent Nondeterministic final state

November 28, 2011 | Comments Off on Intro to Databases: Constraints and Triggers, Triggers Intro | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: