Active Databases: Understanding Triggers, Events, and Conditions, Lecture notes of Computer-Aided Power System Analysis

Download Active Databases: Understanding Triggers, Events, and Conditions and more Lecture notes Computer-Aided Power System Analysis in PDF only on Docsity!

Active Database Concepts

Active Databases

• (^) Active Rules – rules that are automatically triggered by events in the database.

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.

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} ON table [REFERENCING ] [FOR EACH [ROW|STATEMENT]] [WHEN (condition)]

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.

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;

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

1. BEFORE statement trigger
2. For each row a) BEFORE row trigger b) Triggering statement c) AFTER row trigger
3. AFTER statement trigger