Triggers
Tom Kelliher, CS 318
Apr. 5, 2002
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.
Normal forms and schema synthesis/decomposition.
- Trigger concepts.
- PostgreSQL triggers.
- Controlling cascading triggers.
Stored procedures in PostgreSQL.
- A trigger is an element of a database schema with the following form:
ON Event
IF PreCondition THEN
Action
where:
- Event refers to a database modification operation.
- PreCondition must be true for the trigger to ``fire.''
- Action is a list of steps to take if the trigger fires.
- Informal example:
ON Insertion on Transcript
IF Course is Full THEN
Abort Transaction
- Trigger consideration
- When is the PreCondition evaluated, relative to the event?
What is an event? A single SQL statement or an entire transaction?
- Options: Immediate or deferred consideration.
- Trigger execution
- When is the Action executed, relative to the event.
- Possibilities: immediate or deferred.
Obviously, immediate execution can't be paired with deferred
consideration.
- Action options with immediate execution:
- Before event.
- In place of event.
- After event.
- Trigger granularity
- Is the trigger associated with the occurrence of the event or
each tuple affected by the event?
- Statement-level triggers:
- Trigger occurs once per event.
- Occurs even if nothing is modified.
- Good for computing aggregate data.
- Trigger is passed ``before'' and ``after'' images of the
affected tuples as two relations.
- Row-level triggers:
- Trigger occurs multiple times per event.
- Won't occur if nothing is modified.
- Good for checking each affected tuple.
- Trigger is passed ``before'' and ``after'' images of the
affected tuples.
- Multiple enabled triggers
- Ordering of consideration?
- Concurrent or consecutive evaluation/execution.
- Triggers and integrity constraints
- How do triggers interact with
ON DELETE
and
ON CASCADE
clauses of UPDATE
and DELETE
statements
and foreign constraints.
-
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.
- 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();
- Introduction to the anatomy of a PL/pgSQL trigger function.
- ``Safe'' triggers.
- Dependency graphs of triggers.
- Eliminating cycles in the dependency graph.
- Some cycles are safe. Self-limiting triggers: LimitSalaryRaise.
Thomas P. Kelliher
Thu Apr 4 08:23:10 EST 2002
Tom Kelliher