 |
|
Oracle Database Triggers
Oracle Tips by
Burleson
|
Database triggers are the ideal tool for enforcing business rules
that relate directly to data. Each database trigger is associated with
a single table in the database and is configured to fire at a certain
point when data is modified. Typical uses of database triggers include
the following:
-
Checking columns for conformity to complex
rules that cannot be enforced with check constraints.
-
Updating relevant data in other tables.
-
Marking newly created or recently modified rows
for another process to analyze.
-
Ensuring that columns are populated.
-
Signaling that a particular event has occurred.
The real strength of database triggers is that a trigger always
fires when a defined action is performed on the trigger’s associated
table. Even statements executed via the command line in SQL*Plus will
fire a trigger, making triggers the perfect method for enforcing
business rules. Triggers can be written to fire at both statement
level and row level.
Statement-level triggers fire once for each statement (or
transaction) that affects the associated table. For instance,
UPDATE STUDENTS
SET overall_gpa = 4.0;
would fire a statement-level trigger once, no matter how many rows
of data were affected by the statement.
Row-level triggers fire once for each row affected by a statement
that affects the trigger’s associated statement. For instance,
UPDATE STUDENTS
SET overall_gpa = 4.0;
would fire a row-level trigger once for each row in the STUDENTS
table.
Triggers are cued by specific events. A trigger that fires when its
associated table is updated does not fire when a new row is inserted
into the associated table, unless the same trigger is coded to fire
for both updates and inserts on the associated table. Triggers can be
written to fire when DELETE, INSERT, and UPDATE
statements affect the trigger’s associated table.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
