Active Database Concepts
Download
Report
Transcript Active Database Concepts
Active Database Concepts
1
Active Databases
• Active Rules – rules that are automatically
triggered by events in the database.
2
Event-Condition-Action
if(event && condition) {
action;
}
• Event – an event occurs that triggers a rule
• Condition – check to see if the rule should
be executed
• Action – the action to be taken
3
Event
• e.g. INSERT, UPDATE, DELETE.
• Types of Events
– isolated event
– transaction or chain reaction
• Event granularity
– Row level - tuples
– Statement level – statements
4
Condition
• When do you consider the condition?
– Immediate – when the event happens.
– Deferred – at the end of a transaction.
– Detached – in a separate transaction spawned
by the trigger.
5
Immediate Consideration
• Three flavors
– Before – e.g. date modified
– After – e.g. transaction log
– Instead of – e.g. view
• Oracle uses this model.
6
Deferred Consideration
• Check all of the conditions at the end of a
transaction.
• You could have transient data that you don't
want triggering an event.
e.g. Two students switching classes.
7
SQL3 Trigger Syntax
CREATE TRIGGER name
{BEFORE|AFTER} <event> ON table
[REFERENCING <alias list> ]
[FOR EACH [ROW|STATEMENT]]
[WHEN (condition)]
<body>
8
Oracle Syntax
CREATE [OR REPLACE] TRIGGER name
{BEFORE|AFTER|INSTEADOF}
{DELETE|INSERT|UPDATE[OF column_list]} ON
table_name
[ REFERENCING [ OLD AS old_var ]
[ NEW AS new_var ] ]
[FOR EACH ROW [ WHEN (condition)]
trigger PL/SQL body;
9
Timing Options
{BEFORE|AFTER|INSTEADOF}
• BEFORE – before the triggering event makes any
changes to the database. You can alter the
triggering event.
• AFTER – executes after the triggering event is
processed. Can't alter the triggering event
• INSTEAD OF – do something other than the
triggering event. Map an insertion on a view to
physical tables.
10
Triggering Statement
{DELETE|INSERT|UPDATE[OF column_list]} ON
table_name
• The type of SQL statement that fires the
trigger body.
• The name of the table
• UPDATE can limit the firing scope to just
columns.
11
FOR EACH ROW Option
[FOR EACH ROW [ WHEN (condition)]
• FOR EACH ROW - determines if you are
using a statement or row level trigger
• WHEN clause– a boolean condition to
further restrict the trigger.
– You can't use methods or stored procedures.
12
REFERENCING Option
[ REFERENCING [ OLD AS old_var ]
[ NEW AS new_var ] ]
• If you have a row level trigger you can use
:old and :new, or your alias, to reference the
pre-change and post-change values
respectively
• You can only do this with INSERT,
UPDATE, and DELETE tuples
• :old for INSERT?
• :new for DELETE?
13
Trigger Body
• Trigger bodies can contain DML SQL statements
(INSERT, DELETE, UPDATE)
• SELECT INTO or SELECT w/ cursors
• No DDL allowed (CREATE, DROP, ALTER)
• Conditional Predicate
– IF INSERTING THEN … END IF;
– IF UPDATING ('EID') THEN … END IF;
– IF DELETING THEN … END IF;
14
Mutating tables
• Mutating table = table that is currently
being modified by an INSERT, UPDATE,
or DELETE
• You can't look at a table as it is mutating
because you can get inconsistent data.
• Statement-triggers don't have this problem
as long as they aren't fired from a DELETE
CASCADE.
15
Constraining Tables
• Constraining table is a table that a triggering
statement reads using SQL or referential
integrity.
• Triggers can't change PRIMARY,
FOREIGN, OR UNIQUE KEY columns of
a constraining table.
• One exception – BEFORE ROW and
AFTER ROW single row INSERT
statements.
16
Trigger Firing Order
1. BEFORE statement trigger
2. For each row
a) BEFORE row trigger
b) Triggering statement
c) AFTER row trigger
3. AFTER statement trigger
17
Consistency and Termination
• Trigger failure results in a data rollback.
• Triggers can create a chain reaction of
cascading triggers.
• Cascading triggers can create loops.
CREATE OR REPLACE TRIGGER loop_ais
AFTER INSERT ON loop
BEGIN
INSERT INTO loop values(1);
END;
18