 |
|
|
|
|
|
Database triggers allow Oracle to manipulate your tables and data based on set criteria and
database events. This chapter describes what triggers are and what triggers can do. It shows you
the kinds of triggers you can create in Oracle, as well as some examples of common triggers programmers
tend to use in day-to-day work. After reading this chapter, you should be able to incorporate
triggers in your database design to solve common database issues.
What Are Triggers?
Triggers are simply blocks of code Oracle will execute for you automatically based on some action
or event on a database table. Triggers can be used to auto-generate data to insert in a new table
row, to create an auditing trail for table data, or to enforce referential integrity on a set of data
stored across multiple tables. Like stored procedures or user-defined functions, triggers are stored
in the database and are compiled the first time they are called.
Types of Triggers
Oracle provides several types of triggers for you to use based on when the data in the table should
be updated. Triggers can operate on an INSERT, UPDATE, or DELETE on a table, and can perform
their work either BEFORE or AFTER those operations. Triggers can also operate on the statement
level, or on each row of data being modified by including a FOR EACH ROW directive in the trigger
definition.
You can also write INSTEAD OF triggers that allow you to perform operations on a view that are
not normally allowed, such as deleting from a view that joins many tables. Starting with Oracle 8i,
you can write globally scoped triggers that fire on events for all users at the DATABASE level, or
events that fire for each user at the SCHEMA level. You can have as many triggers as you want on a
table, and Oracle will execute them in the proper order based on the trigger definition.Schema- and database-level triggers can fire on the DDL events CREATE, ALTER, and DROP, as well as
database-level events like SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN. You should try to
avoid creating these types of triggers, as they can add overhead to the execution of your SQL. These triggers
should be left for DBAs to create if needed.Order of Execution
Triggers execute based on trigger type. Oracle will execute all BEFORE statement-level triggers, then all
BEFORE triggers operating on each row, then all AFTER triggers operating on each row, and finally all
AFTER statement-level triggers. While the execution order is set by trigger type, Oracle does not guarantee
the order in which the triggers of any given type will be executed. You should not write triggers that
assume other triggers have been fired before its operations can complete successfully. If you want finegrained
control over the order of operations performed in a trigger, you should create a single trigger
and perform those operations by calling functions or procedures from within the trigger body.
The order of execution is as follows:
? All BEFORE statement-level triggers
? All BEFORE triggers with a FOR EACH ROW designation
? All AFTER triggers with a FOR EACH ROW designation
? All AFTER statement-level triggers
Working with Triggers
Now that you’ve been introduced to triggers, the following sections show you how to create and use
them. You will see how to create different types of triggers, modify them, delete them, and perform
some advanced operations. You will also learn how to create triggers in Java.
Naming Triggers
Trigger names must be unique within a schema, but two triggers in different schemas can have the same
names. Triggers can also be named the same as other objects in the schema, but for clarity, a naming convention
that includes the object name and function should be employed to easily locate the object in
your schema. All of the following are valid trigger names:
? updateContentID
? delete_related_content
? insertUpdateWebContent_trigger
Triggers are generally defined by three variables: the operation that should activate
the trigger (INSERT, UPDATE, DELETE), the timing of the trigger’s activation (BEFORE,
AFTER), and if the trigger should operate on the statement as a whole or on each row
of data (FOR EACH ROW).
438
Chapter 19
Permissions for Creating Triggers
To create triggers on tables in your schema, you must have the CREATE TRIGGER permission. To create
triggers in other schemas, you must have the CREATE ANY TRIGGER system permission. To create triggers
on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system permission.Creating Triggers
The generic form for creating a basic trigger is as follows:
CREATE [OR REPLACE] TRIGGER triggerName [BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON tableName [FOR EACH ROW [WHEN condition]] triggerBody;
If you add the OR REPLACE option, the trigger will be dropped and re-created if it already exists. You can
specify if the trigger will execute BEFORE or AFTER the triggering event is fired, as well as what the triggering
event should be. By using the FOR EACH ROW option, you can also specify if the trigger should fire
for each row that is being updated, or if it should fire only once per operation. Additionally, a WHEN
clause can be included to add even more granularity to the triggering condition. You can specify multiple
triggering events for a trigger by connecting them with an OR. For example, you can create a trigger
that will fire after an INSERT, UPDATE, or DELETE on a table by stating the following:
CREATE OR REPLACE TRIGGER triggerName AFTER INSERT OR UPDATE OR DELETE
on tablename
triggerBody;
One of the most common uses of triggers is to implement an auto-number primary key for a table. This
is done using a sequence to generate the integer for the primary key column and using a trigger to
update the primary key field in the table. In this example, you will be creating a table to hold content for
a Web site, and creating a sequence and trigger to set the ID column.
First, you need to create the table and sequence. The code to create a sequence that starts with 1 and
increments by 1 and caches 10 numbers each execution is as follows:
create sequence contentSeq start with 1 increment by 1 cache 10;
a table to hold the Web site content by issuing the following SQL statement. The table uses the new
Oracle XMLTYPE to store XML data:SapereOnLine |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
