 |
|
The Data Dictionary and
Trigger Source Code
Oracle Tips by
Burleson
|
When you create a trigger, Oracle stores the source code for the
trigger within the data dictionary. This code is stored in the
ALL_TRIGGERS view. The structure of the view is shown in Listing
7.10.
Listing 7.10 The structure of the ALL_TRIGGERS view.
owner varchar2 (30)
trigger_name varchar2 (30)
trigger_type varchar2 (16)
triggering_event varchar2 (26)
table_owner varchar2 (30)
table_name varchar2 (30)
referencing_names varchar2 (87)
when_clause varchar2 (2000)
status varchar2 (8)
description varchar2 (2000)
trigger_body long
If you’re already familiar with the structure of the ALL_SOURCE
view (the view that shows you where the source code for procedures,
functions, and packages is located), you’ll note one significant
difference—the source code for a trigger is stored in a long
variable as a single string and not on a line-by-line basis like
procedures, functions, and packages.
Typical Uses for Triggers
Database triggers are an ideal tool for enforcing business rules
that are directly related to data. There are many common uses of
database triggers, including:
-
Enforcing business rules that cannot be
enforced with check constraints
-
Updating data in other tables
-
Marking rows for processing or rows that have
been processed
-
Signaling that an event has occurred
Each of these uses is described in the following text, but keep in
mind that this is certainly not an exhaustive list of uses for
database triggers. Every business and every system has different
rules.
Enforcing Complex Business Rules
The most complicated rule that can be enforced with a check
constraint is a simple mathematical expression. If business rules
didn’t exceed this level of complication, database triggers probably
wouldn’t exist. In the real world, business rules are often more
complicated than simple equations. It’s extremely common for
application developers to use a database trigger to enforce an
extremely complicated rule.
Updating Relevant Data
If related data is kept in multiple tables (a fairly common
occurrence in Oracle systems), it’s desirable to use a database
trigger to keep related data in sync. Care must be taken when dealing
with referential integrity constraints, because references to indexed
columns can cause mutating table errors if the trigger isn’t
structured properly.
Marking Rows for Processing
In some instances, a row-level database trigger is used to make
certain that new and modified rows of data are distinguishable from
rows that have had certain processing performed.
A similar approach assumes that all unmarked rows haven’t been
processed. A trigger processes each row of data and flags the row to
ensure that processing isn’t repeated.
Signaling an Event
A trigger can be used to signal that a particular event has
occurred. This can be accomplished by using the DBMS_Alert
package. This package is discussed in Chapter 9.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
