28/36

22 Triggers

This chapter discusses triggers, which are procedures stored in PL/SQL or Java that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.

This chapter contains the following topics:

Parts of a Trigger A trigger has three basic parts: A triggering event or statement

A trigger restriction

A trigger action Figure 22-3 represents each of these parts of a trigger and is not meant to show exact syntax. The sections that follow explain each part of a trigger in greater detail. Figure 22-3 The REORDER Trigger

Description of "Figure 22-3 The REORDER Trigger"



The Triggering Event or Statement A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following: An INSERT , UPDATE , or DELETE statement on a specific table (or view, in some cases)

A CREATE , ALTER , or DROP statement on any schema object

A database startup or instance shutdown

A specific error message or any error message

A user logon or logoff For example, in Figure 22-3, the triggering statement is: ... UPDATE OF parts_on_hand ON inventory ... This statement means that when the parts_on_hand column of a row in the inventory table is updated, fire the trigger. When the triggering event is an UPDATE statement, you can include a column list to identify which columns must be updated to fire the trigger. You cannot specify a column list for INSERT and DELETE statements, because they affect entire rows of information. A triggering event can specify multiple SQL statements: ... INSERT OR UPDATE OR DELETE OF inventory ... This part means that when an INSERT , UPDATE , or DELETE statement is issued against the inventory table, fire the trigger. When multiple types of SQL statements can fire a trigger, you can use conditional predicates to detect the type of triggering statement. In this way, you can create a single trigger that runs different code based on the type of statement that fires the trigger. Trigger Restriction A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown . In the example, the trigger restriction is: new.parts_on_hand < new.reorder_point Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount. Trigger Action A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when the following events occur: A triggering statement is issued.

The trigger restriction evaluates to true. Like stored procedures, a trigger action can: Contain SQL, PL/SQL, or Java statements

Define PL/SQL language constructs such as variables, constants, cursors, exceptions

Define Java language constructs

Call stored procedures If the triggers are row triggers, the statements in a trigger action have access to column values of the row being processed by the trigger. Correlation names provide access to the old and new values for each column.