What's a trigger? Well, Trigger was the name of Roy Rogers' horse, but in the Oracle world, a trigger is an important
system-level component that allows you to associate a "hunk of code"
(usually PL/SQL or Java) with a specific system event, namely DML
(SQL insert, update and delete) statements.
A trigger is like an IDMS user exit, an opening in the code where
you can branch out and "do your own thing", prior to performing the
insert, update or delete. The problems with Oracle triggers
includes:
Oracle defines triggers as "procedures that are stored in the
database and implicitly run, or fired, when something happens."
[Page 15-1, Application Developer's Guide] What is the
"something" that happens to make a trigger fire? There are 12 "some
things"
that can cause triggers to fire: Before/After during
Insert/Update/Delete on a Row/Table (which leads to the 2x3x2=12
types).
The DML triggers are the best known, but there are more
available since Oracle8i was released. The additional triggers,
or types of triggers, are instead-of triggers, database event
triggers, and DDL triggers. Instead-of triggers are used when
views are involved, and are a good way of avoiding the mutating
table trigger-related error.
Database event triggers can be divided into two categories:
on the database, where triggers occur for all users, and on a
schema, where triggers occur for that user. Data definition
types of triggers can be used to capture events related to DDL
statements such as create, drop, and alter.
Oracle places a size limit of 32KB on a trigger statement.
How "big" is 32KB of data? Up to the question mark, this article
used about 25KB, using around 870 words and over 4,000
characters, just to give you a rough idea of how much code you
can write under 32KB. Is there a way around the 32KB limit? Yes,
you can call external procedures.
If you do not use LONG or LONG RAW data types, any restrictions
concerning these are transparent to you. Several other
restrictions and one interesting restriction has to do with the
order in which triggers are fired.
So what are some design issues when considering the use of
triggers? When considering which type of trigger to use in a DML
operation, you want to avoid having a hair trigger, that is,
firing a trigger when it is not the appropriate time to fire it.
Conversely, you do not want the correct trigger to fire late.
Suppose you have a banking application with a trigger that
checks to see if the DML operation is occurring after business
hours (assuming that it should only be performed during business
hours). The DML operation involves updating a million records.
It does not make sense to let Oracle perform the update, only to
have it canceled or rolled back because you used an AFTER
trigger instead of a BEFORE trigger to check the time of day.
The general rule here would be to check or enforce the business
logic BEFORE using database resources. In this example, an AFTER
trigger can be used to record what happened.
Oracle fires all triggers of a type, then all triggers of
another type, and so on. You have no control over which type is
fired first, and whichever type is fired after the first type
will see any changes made by the first type, and that cascades
down (third type sees changes made by the second type, and so
on).
Finally, the mutating table error, ORA-04091 table
owner.table_name is mutating, trigger/function may not see it
has made DBAs trigger-happy in ways we would rather they not be
exposed to. . .
There are a great many things you can do with triggers,
whether they are based on DML statements or system events. As a
developer or DBA (or both), there is no such thing as having too
many tricks up your sleeve.
In terms of job or role separation, you can think of the DML
triggers as being in the purview of the developer, and the
system event triggers being in the DBA's, but a good DBA should
possess some decent programming skills of his or her own, and
that's where knowing how to avoid problems with DML triggers
comes into play. Being and staying well-informed on the use (and
limitations) of triggers
Oracle trigger tips, and many other Oracle performance metrics are discussed in
my book "Oracle
Tuning" by Rampant TechPress. You can buy it directly from
the publisher and save 30% at this link:
