Chapter 7
A trigger is an element of the database schema that has the following structure:
On event If precondition Then action.
Event is a request for the execution of a particular database operation (insert, delete, etc.)
Precondition is an expression that evaluates to true or false.
Action is a statement of what needs to be done when the trigger is fired.
Trigger uses:
Constraint maintenance.
Enforce business rules.
Monitoring. (sensor based systems)
Maintenance of auxiliary cached data. For example, materialized views.
Separate core program logic from exception handling.
Example (in pseudocode):
On inserting a row in course registration table
If over course capacity
Then abort registration transaction
Two time when triggers can be invoked:
Immediately when the triggering event is requested.
Deferred until after the transaction commites.
Also, when triggers are considered immediately their action may be executed
immediately or it may be deferred until the end of the transaction.
Also, when triggers are considered immediately the trigger can be executed after the
triggering event is carried out, before the triggering event is carried out, or instead of the
triggering event.
Trigger Granularity:
Trigger invoked on change to a row or when a statement is executed.
For example, think of on delete cascade as a trigger.
Exam Question #3: Contrive a database design with two tables, one table with a foreign
key referencing the other table. Supply create table statements for those two tables.
Supply pseudocode for triggers which implement
1.“on delete cascade”
2. “on update cascade”
Page 258
Example 7.3.1 (Business Rule Enforced with a BEFORE trigger)
Create Trigger roomcapacitycheck
