Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter