 |
|
Oracle Stored Objects
Oracle Tips by
Burleson
|
The release of PL/SQL 2.0 and Oracle7 revolutionized the Oracle
world. Previously, all code for enforcing business rules was stored at
the application level, often leading to multiple occurrences of the
same code in different applications. This was a nightmare when a
business rule changed, since it was extremely difficult to track which
applications enforced which rules.
Oracle7 allowed developers to store code at the database level,
introducing stored procedures, functions, and packages, as well as
database triggers. These objects must exist in only one place (inside
the database). Not only did this reduce maintenance costs and
duration, it also speeded application development time because code
only needed to be written once. In many IS departments that use
Oracle7, most or all enforcement of business rules is now handled
through triggers and stored procedures.
Database Triggers
Database triggers are objects that are closely associated
with tables. There are four distinct times at which database triggers
fire, twice at the statement level and twice at the row level, as
shown in Table 2.5.
|
Table 2.5 When database triggers fire |
|
Trigger Type |
Fires |
|
before |
The statement level trigger fires before a
statement is executed on the trigger’s associated table. |
|
after |
The statement level trigger fires after a
statement is executed on the trigger’s associated table. |
|
before row |
The row level trigger fires once for each row
affected by a statement executed on the trigger’s associated
table. The trigger fires prior to the execution of the statement
and can alter the contents of the data. |
|
after row |
The row level trigger fires once for each row
affected by a statement executed on the trigger’s associated
table. The trigger fires following the execution of the statement.
|
Database triggers can fire on INSERT, UPDATE, and
delete events. Thus, there are 12 possible triggers that can be
written for a table. The types of database triggers execute in a
specific order, as illustrated in Figure 2.11.
Database triggers are covered in more detail in Chapters 7.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
