330 likes | 955 Views
Active Database Concepts. Active Databases. Active Rules – rules that are automatically triggered by events in the database. 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
E N D
Active Databases • Active Rules – rules that are automatically triggered by events in the database.
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
Event • e.g. INSERT, UPDATE, DELETE. • Types of Events • isolated event • transaction or chain reaction • Event granularity • Row level - tuples • Statement level – statements
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.
Immediate Consideration • Three flavors • Before – e.g. date modified • After – e.g. transaction log • Instead of – e.g. view • Oracle uses this model.
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.
SQL3 Trigger Syntax CREATE TRIGGER name {BEFORE|AFTER} <event> ON table [REFERENCING <alias list> ] [FOR EACH [ROW|STATEMENT]] [WHEN (condition)] <body>
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;
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.
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.
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.
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?
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;
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.
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.
Trigger Firing Order • BEFORE statement trigger • For each row • BEFORE row trigger • Triggering statement • AFTER row trigger • AFTER statement trigger
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;