Active Database Concepts

1
Active Database Concepts
2
Active Databases

• Active Rules rules that are automatically
  triggered by events in the database.

3
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

4
Event

• e.g. INSERT, UPDATE, DELETE.
• Types of Events
• isolated event
• transaction or chain reaction
• Event granularity
• Row level - tuples
• Statement level statements

5
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.

6
Immediate Consideration

• Three flavors
• Before e.g. date modified
• After e.g. transaction log
• Instead of e.g. view
• Oracle uses this model.

7
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.

8
SQL3 Trigger Syntax

• CREATE TRIGGER name
• BEFOREAFTER lteventgt ON table
• REFERENCING ltalias listgt
• FOR EACH ROWSTATEMENT
• WHEN (condition)
• ltbodygt

9
Oracle Syntax

• CREATE OR REPLACE TRIGGER name
• BEFOREAFTERINSTEADOF
• DELETEINSERTUPDATEOF column_list ON
  table_name
• REFERENCING OLD AS old_var
• NEW AS new_var
• FOR EACH ROW WHEN (condition)
• trigger PL/SQL body

10
Timing Options

• BEFOREAFTERINSTEADOF
• 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.

11
Triggering Statement

• DELETEINSERTUPDATEOF 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.

12
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.

13
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?

14
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

15
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.

16
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.

17
Trigger Firing Order

• BEFORE statement trigger
• For each row
• BEFORE row trigger
• Triggering statement
• AFTER row trigger
• AFTER statement trigger

18
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