28 Active Databases

Dr R. Baskaran

Conventional (Passive Databases)

Data model, usually relational
Transaction model – Passive update principle Example of further real world problem
Inventory control – reordering of items when quantity in stock falls below threshold.
Travel waiting list – book ticket as soon as right kind is available
Stock market – Buy/sell stocks when price below/above threshold

Rules

specify actions that are automatically triggered by certain events
generalised model: EVENT-CONDITION-ACTION (ECA)
event(s): database update operation(s)
condition: determines whether the rule is to be executed after the event occurred
action: action to be taken
SQL command(s)
external program
rules are sometimes called triggers
however, some systems make distinctions between them (Postgres)

Triggers

What is a trigger?

Trigger is like a procedure that is automatically invoked by the DBMS in response to specified changes to data base.
Trigger is like a ‘Daemon that monitors a data base, and is executed when the data base is modified in a way that matches the event specification.
A data base that has a set of associated triggers is called an active data base.

Event-Condition-Action (ECA)

Event occurs in databases
addition of new row, deletion of row by DBMS
Conditions are checked
SQL condition
Actions are executed if conditions are satisfied
SQL + procedures
All data actions performed by the trigger execute within the same transaction in which the trigger fires,
Cannot contain transaction control statements (COMMIT,SAVEPOINT, ROLLBACK)

Motivation

Database Triggers in SQL

Not specified in SQL-92, but standardized in SQL3(SQL1999)
Available in most enterprise DBMSs (Oracle, IBM DB2, MS SQL server) and some public domain DBMSs (Postgres)
but not present in smaller desktop (Oracle Lite) and public domain DBMS (MySQL)
Some vendor DBMS permit native extensions to SQL for specifying the triggers
E.g. PL/SQL in Oracle, Transact SQL in MS SQL Server
Some DBMS also general purpose programming language instead of SQL
Eg. C/C++ in Poet, Java in Oracle, C#/VB in SQL Server
Some DBMS extend the triggers beyond tables
for example also to views as in Oracle

Types of SQL Triggers

How many times should the trigger body execute when the triggering event takes place?
Per statement: the trigger body executes once for the triggering event. This is the default.
For each row: the trigger body executes once for each row affected by the triggering event.

Issues about active databases

general model (remember): ECA
event
in general INSERT, UPDATE, DELETE
can be more specific (see “update of salary on employee”)
condition
could be empty when solely the event triggers
row-level rule: the rule is triggered for each tuple for which the condition is satisfied
statement-level rule: the rule is triggered only once for each triggering statement
action
SQL statement or external procedure

Statement and Row Triggers

Example 1: Monitoring Statement Events

SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, ‘EDUCATION’, ‘NEW YORK’);

Execute only once even if multiple rows affected

Example 2: Monitoring Row Events

SQL> UPDATE emp

2 SET sal = sal * 1.1

3 WHERE deptno = 30;

Execute for each row of table affected by event

Firing Sequence of Database

SUMMARY