Active Database Concepts 1 Active Databases Active Rules

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

SQL 3 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$

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$

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