Triggers

Tom Kelliher, CS 318

Apr. 5, 2002

Administrivia

Announcements

Assignment

Read Chapter 10. Refer to the PostgreSQL online documentation for triggers and stored procedures Chapter 24 of the Programmer's Guide. Project implications: Your integrity constraints and FDs should be maintained via triggers and PL/pgSQL stored procedures.

From Last Time

Normal forms and schema synthesis/decomposition.

Outline

1. Trigger concepts.

2. PostgreSQL triggers.

3. Controlling cascading triggers.

Coming Up

Stored procedures in PostgreSQL.

Trigger Concepts

1. A trigger is an element of a database schema with the following form:

   ON Event
      IF PreCondition THEN
         Action
   

   where:
   1. Event refers to a database modification operation.

   2. PreCondition must be true for the trigger to ``fire.''

   3. Action is a list of steps to take if the trigger fires.

2. Informal example:

   ON Insertion on Transcript
      IF Course is Full THEN
         Abort Transaction
   

3. Trigger consideration
   1. When is the PreCondition evaluated, relative to the event?

      What is an event? A single SQL statement or an entire transaction?

   2. Options: Immediate or deferred consideration.

4. Trigger execution
   1. When is the Action executed, relative to the event.

   2. Possibilities: immediate or deferred.

      Obviously, immediate execution can't be paired with deferred consideration.

   3. Action options with immediate execution:
      1. Before event.

      2. In place of event.

      3. After event.

5. Trigger granularity
   1. Is the trigger associated with the occurrence of the event or each tuple affected by the event?

   2. Statement-level triggers:
      1. Trigger occurs once per event.

      2. Occurs even if nothing is modified.

      3. Good for computing aggregate data.

      4. Trigger is passed ``before'' and ``after'' images of the affected tuples as two relations.

   3. Row-level triggers:
      1. Trigger occurs multiple times per event.

      2. Won't occur if nothing is modified.

      3. Good for checking each affected tuple.

      4. Trigger is passed ``before'' and ``after'' images of the affected tuples.

6. Multiple enabled triggers
   1. Ordering of consideration?

   2. Concurrent or consecutive evaluation/execution.

7. Triggers and integrity constraints
   1. How do triggers interact with ON DELETE and ON CASCADE clauses of UPDATE and DELETE statements and foreign constraints.

PostgreSQL Triggers

1. CREATE TRIGGER syntax in PostgreSQL:

   CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
      ON table FOR EACH { ROW | STATEMENT }
      EXECUTE PROCEDURE func ( arguments );
   

   EVENT is one of INSERT, DELETE, or UPDATE. STATEMENT level triggers are not currently supported.

   Of course, the PL/pgSQL func must appear before the trigger naming it.

2. Example:

   CREATE FUNCTION RaiseCheck () RETURNS OPAQUE AS '
      BEGIN
         IF NEW.Salary > 1.05 * OLD.Salary THEN
            -- Excessive salary raise - abort transaction.
            RAISE EXCEPTION ''% given an excessive raise'', OLD.EmpName;
         END IF;
         -- Salary increase OK, proceed with transaction.
         RETURN NEW;
      END;
   ' LANGUAGE 'plpgsql';
   
   CREATE TRIGGER LimitRaises BEFORE UPDATE ON Employee 
      FOR EACH ROW EXECUTE PROCEDURE RaiseCheck();
   

3. Introduction to the anatomy of a PL/pgSQL trigger function.

Controlling Cascading Triggers

1. ``Safe'' triggers.

2. Dependency graphs of triggers.

3. Eliminating cycles in the dependency graph.

4. Some cycles are safe. Self-limiting triggers: LimitSalaryRaise.