Chapter 10

Developing and Using Database Triggers

In this chapter, you will cover the following areas of developing and using database triggers:

• developing database triggers
• managing triggers

Sometimes you want something to happen when a certain type of database change happens. For example, a table may contain information pertaining to a sales order. Several different applications read the sales order and do something with it. Dependencies exist between the different applications, so that the shipping application cannot process a record until the accounts payable application marks it paid, which cannot happen until the invoicing application makes and sends a bill for it, and so on. Each of these applications may update an order status flag stored in a column on the table. However, let’s say someone wants to track the number of days it takes for the record to go from invoicing to delivery. You decide the best way to handle this situation is to populate a history table with the sales order, the new and old status, and the date the record changed. A database trigger will help you do the job. This chapter will explain how database triggers work and what other things they can do. How you develop and maintain database triggers using Procedure Builder and other means will also be covered. All told, the content of this chapter comprises 24% of OCP exam 2 test content.

In this section, you will cover the following points regarding developing database triggers:

• Identifying different trigger types
• Creating statement triggers
• Creating row triggers
• Dictionary information about triggers

A trigger is a database object directly associated with a particular table that fires whenever a specific statement or type is issued against that table. The types of statements that fire triggers are query and data manipulation statements, like select, update, delete and insert statements. In this section, you will learn how to distinguish a database trigger from other types of triggers in Oracle, along with the syntax for defining triggers. The difference between a statement trigger and a row trigger in the Oracle database will be presented as well. For simplicity’s sake, assume Procedure Builder is used to develop triggers, although you will be presented with the correct SQL statement syntax for building triggers even though Procedure Builder builds it for you.

One favorite question asked to distinguish Oracle server-side PL/SQL developers from Developer/2000 professionals is "what is the difference between a database trigger, a stored procedure, and a Forms trigger?" You haven’t covered Oracle Forms yet, but now is as good a time as any to start distinguishing one trigger from another.

Part of the answer to this question lies in where the trigger is stored. A database trigger is stored as a compiled object within the Oracle database. Its source code can be found in the Oracle data dictionary, and when running, its executable is loaded into Oracle’s system global area, or SGA for short. The SGA is the memory area Oracle uses as it runs. The stored procedure is stored similarly, as compiled code in the database that gets loaded into SGA when necessary for use in an application. Another factor that makes a trigger similar to a stored procedure is that both are written at least partly with PL/SQL.

However, the substantial difference between a trigger and a stored procedure is when they run. A trigger runs automatically when statements of a certain type execute. For example, if you set up a trigger to fire whenever a user updates a record in the EXCH_RATE table, no action is necessary to get that trigger to fire other than updating a record in EXCH_RATE. In other words, you don’t need to issue a call explicitly to the trigger. On the other hand, a stored procedure only runs when you call it explicitly from another procedure, or with the PL/SQL interpreter in Procedure Builder, or with the execute command in SQL*Plus.

Finally, consider the Forms trigger. Use of Forms triggers is covered extensively in chapter Z. For now, simply understand that a Forms trigger consists of PL/SQL code that runs when a specific action takes place. So, in this respect a Forms trigger is similar to a database trigger. The difference is that a Forms trigger is stored in the Forms application, in turn stored on a client machine, not the database. The Forms trigger also fires when a specific event occurs on the application GUI, such as a button being pressed. In contrast, the database trigger fires when a database event happens, such as a new row of data being inserted in the table.

The basic type of database trigger is a statement trigger. It will fire every time a triggering statement occurs on the table to which the trigger is attached. Let’s assume you want to monitor the EXCH_RATE table delete activity to identify when exchange rates are removed from the table. You create a history table that logs the user that changes the EXCH_RATE table, along with the date/time information and a comment in VARCHAR2 format for tracking when data is removed. Then you set up a trigger to populate the new EXCH_RATE_HIST table.

Trigger SQL code is sort of a hybrid between a database object creation statement (like create table) and straight PL/SQL code. Procedure Builder can be used to develop the trigger, as demonstrated in figure 10-1. However, since Procedure Builder builds many of the basic SQL syntactic constructs required to compile a database trigger, the following code block demonstrates that basic SQL syntax. Compare the code block to figure 10-1 in order to understand what work Procedure Builder does for you behind the scenes.

CREATE OR REPLACE TRIGGER rate_hist_trigger_01
BEFORE delete ON exch_rate
BEGIN
INSERT INTO exch_rate_hist (chg_user, chg_date_time, comment)
VALUES (user, to_char(sysdate,’YYYY-MM-DD HH:MIAM’),
’Exchange rates removed from table on this date’);
END;

Now, consider the syntax used to create this trigger. First, you consider the create or replace trigger trig_name portion. This code indicates the creation of a new trigger, but also states that if a trigger of the same name already exists, Oracle should go ahead and replace it. Re-examine Figure 10-1 now, and notice that you don’t need to specify this to Procedure Builder in order for it to create your trigger. The trig_name portion allows you to identify the trigger by a name of your making. Optionally, you can identify the user who will own this trigger. Ideally, it will be the same schema owning the table you attach the trigger to. Notice also in Procedure Builder that if you do not want to write your own name for the trigger, the tool will generate one for you. This is not the case with writing a trigger in SQL*Plus -- Procedure Builder generates this trigger name, not the Oracle database. So, if you are writing trigger code in a flat file and intend to load it using SQL*Plus, do not forget a trigger name or else your code will fail.

The next big line to consider is before delete on tbl_name. This line identifies three big components. First is when the trigger fires, either before the triggering statement is processed by Oracle or after it is processed by Oracle. Should you want to run the trigger after the delete statement takes place, change the before keyword to after. The second important point about this line of the trigger creation statement is the delete keyword. You can set up a trigger to run as the result of an update or insert statement in addition to delete. Procedure Builder allows you to set up this information with the use of the checkboxes and text box in the middle portion of figure 10-1, and its use should be fairly self-explanatory. If you are confused, you should try to create a trigger using the database trigger editor module of Procedure Builder, but remember first to connect to the database.

In the case of an update trigger, you can further narrow the trigger scope to fire only when a particular column value is changed with the use of update of col_name. Also, you can set up a trigger to do multiple activities by specifying several triggering statements with the use of update or delete or insert, or a portion thereof. The following code block illustrates some of the different options for creating a statement trigger.

CREATE OR REPLACE TRIGGER stacy.rate_hist_trigger_01
BEFORE update on (exch_rate,valid_date) ON exch_rate
BEGIN
…
END;

CREATE OR REPLACE TRIGGER stacy.rate_hist_trigger_01
BEFORE insert OR update OR delete ON exch_rate
BEGIN
IF (inserting) THEN…
ELSIF (updating) THEN…
ELSIF (deleting) THEN…
END IF;
END;

The second example in the preceding block illustrates an interesting point about database triggers – you can set them up to fire for multiple triggering events. Within the trigger’s PL/SQL, you can set up to distinguish different activities that correspond to the different events causing the trigger to fire. The keywords inserting, updating, and deleting are defined for you in the DBMS_STANDARD package, and can be used in a nested if-then statement as the test criteria for branching your code for multiple triggering events. These items are called conditional predicates.

Use of updating, inserting, or deleting in your trigger segues into the final area of the trigger for you to consider – the PL/SQL block that executes as a result of the triggering event. Any valid PL/SQL statement is acceptable in the trigger body. Procedure Builder allows you to develop your trigger body within the database trigger editor module, and again simplifies your work by putting together the begin and end keywords for you. Many SQL statements are permitted within a trigger body as well. Such statements include select, update, delete or insert. The same restrictions on DDL statements (such as create table or truncate table) that are present in PL/SQL on stored procedures applies to triggers as well.

Sometimes with trigger development you want to have more precision over work the trigger will accomplish than a statement trigger allows. For the sake of example, say you want to eliminate the VALID_DATE column from your EXCH_RATE table and simply have EXCH_RATE list the "from" and "to" currency along with an exchange rate, and you will simply assume the rate is valid. Your batch process will then come in and update the exchange rate every day, let’s pretend it does so with only one update statement. You decide later you want a history table that gives "from" currency, "to" currency, an old rate, a new rate, and the date the rate changed. However, these requirements may be beyond what a statement trigger provides. Remember, your statement trigger fires once for the update of the entire table, while you want a trigger that does something for every row.

A row trigger gives this functionality. A row trigger adds some syntax to the statement trigger to identify a method for accessing row data in the trigger’s own table, both before the triggering statement executes and after. The following code block illustrates the syntactic changes that must occur in order for the statement trigger to become a row trigger. Pretend that for timestamp reasons you want to store your change data as a VARCHAR2 string in the EXCH_RATE_HIST table.

CREATE OR REPLACE TRIGGER stacy.rate_hist_trigger_01
AFTER update ON exch_rate
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
INSERT INTO exch_rate_hist (chg_user, chg_date_time, from_curcy,
to_curcy, old_rate, new_rate,
comment)
VALUES (user, to_char(sysdate,’YYYY-MM-DD HH:MIAM’), :old.from_curcy,
:old.to_curcy, :old.exch_rate, :new.exch_rate,
’Exchange rates updated from table on this date’);
END;

Several neat things become possible with row triggers. First, your trigger gains the ability to access row data involved in the triggering statement, both as it existed before the triggering statement runs, and after. This ability gives you tremendous flexibility. The two lines in the trigger creation statement to pay attention to for row triggers are referencing old as old new as new and for each row. For syntactic correctness, put these statements in this order in your trigger. When using Procedure Builder, you merely check the box next to "for each row" on the database trigger editor module interface and the options for creating a row trigger are available to use.

Notice something peculiar about the code block also, the references to :old.col_name or :new.col_name. The old and new keywords can be used to identify both the old version and new version of column data in rows that are changing. These keywords must be preceded with a colon in your trigger PL/SQL. You can define different words to replace old and new in the column references line starting with the referencing keyword. You needn’t include the line starting with referencing if you decide not to change your keywords old and new, and those keywords will still be available for you so long as you specify the trigger is a row trigger.

Finally, if you return your attention briefly back to figure 10-1, you will see all the interface features Procedure Builder gives for building row triggers. You can change the name used for referencing old and new versions of row and column data with text boxes. Another text body is preceded with the phrase "when". This box corresponds with a feature in row triggers allowing you to restrict when the trigger fires based on a SQL condition that must be resolved in order for Oracle to fire the trigger. Should you want to use a when clause in Procedure Builder, simply add the restriction to the text box after the "when" phrase. If data for any row change causes the logic for the when clause to test FALSE, the trigger is not fired for the row. The when clause is checked for all rows, however. The code block produced by your inclusion of a when clause that causes the trigger only to fire when either the "from" currency or the "to" currency is the US dollar (represented by USD in the table) is listed here.

CREATE OR REPLACE TRIGGER stacy.rate_hist_trigger_01
AFTER update ON exch_rate
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
WHEN (new.from_curcy = ‘USD’ OR new.to_curcy = ‘USD’)
BEGIN
INSERT INTO exch_rate_hist (chg_user, chg_date_time, from_curcy,
to_curcy, old_rate, new_rate,
comment)
VALUES (user, to_char(sysdate,’YYYY-MM-DD HH:MIAM’), :old.from_curcy,
:old.to_curcy, :old.exch_rate, :new.exch_rate,
’Exchange rates updated from table on this date’);
END;

Several dictionary views are available to show information about the triggers on a database. They include DBA_TRIGGERS, USER_TRIGGERS, and ALL_TRIGGERS. These different views all have alternate scope, including all triggers on a database, all triggers on a database owned by the current user, and all triggers on a database the current user can see. These views contain basically the same columns, with the exception of the OWNER column displaying trigger owner and the TABLE_OWNER column listing the owner of the table the trigger is attached to. These two appear on only the DBA_TRIGGERS and ALL_TRIGGERS views. Other columns in the DBA_TRIGGERS view appear here. If the column datatype is unusual in any way, the datatype is listed:

• TRIGGER_NAME The name of the trigger.
• TRIGGER_TYPE Whether the trigger is a statement or row trigger.
• TRIGGERING_EVENT The event that causes the trigger to fire, either insert, update, or delete.
• TABLE_NAME The table to which the trigger is attached.
• REFERENCING_NAMES The old or new referencing names you have chosen for the trigger. NULL for statement triggers.
• WHEN_CLAUSE The when clause that determines if the trigger will fire for that row. NULL for statement triggers. Datatype VARCHAR2(2000).
• STATUS Whether the trigger is enabled or disabled. When disabled, the trigger will not fire.
• DESCRIPTION Contains basic information about the trigger, including everything after create or replace up to the PL/SQL section begin. Datatype VARCHAR2(2000).
• TRIGGER_BODY Contains all PL/SQL code in the body of your trigger. Datatype LONG.

DBA_TRIGGER_COLS, USER_TRIGGER_COLS, and ALL_TRIGGER_COLS are another set of views you can use to find out information about specific columns used in triggers. This view has several columns designed to show the use of columns within the trigger, such as those involved in the column-specific update that can be used to determine if a trigger will fire. This view is the same across the three levels of scope provided by USER_, ALL_, and DBA_, save that only the latter two have the TRIGGER_OWNER and TABLE_OWNER columns. The rest of the columns of the view are shown in the following list, along with an explanation of their usage.

• TRIGGER_NAME The name of the trigger.
• TABLE_NAME The table to which the trigger is attached.

• COLUMN_NAME The name of the column used by the trigger
• COLUMN_LIST A YES or NO value set if the column is used in a for update of clause on the trigger definition.
• COLUMN_USAGE Displays the usage of the column both as a referenced value (ie, new or old) and as a parameter to a stored procedure or function call (in, out, or in out).

In this section, you will cover the following points about managing triggers:

• Managing trigger development
• Security issues and trigger availability
• Trigger firing mechanisms
• Creating triggers that complement the Oracle database

With the initial design factors in place, you should now consider aspects of managing trigger development, maintenance, and usage. This section covers the facts you should know about the development of triggers, such as how many triggers can be out there on one table, considerations you need to make on triggers that fire other triggers, and other items. In addition, the security and permissions available to limit trigger development will be covered, along with how you limit trigger availability. Finally, you will consider how triggers fire, and how you can manage that aspect of trigger development as well.

Several factors govern the development of triggers. First of all, within the categories of triggers presented in the prior section, there are six possible sub-categories. Three of these categories are update, delete and insert, the types of statements that induce trigger firing. These three categories then are matrixed across two categories determined by when the trigger fires with respect to the triggering event. Thus, you can have 12 triggers on a table for Oracle7 databases, 1 of each type. To explain further, the types are:

• before update statement trigger

• after update statement trigger

• before insert statement trigger

• after insert statement trigger

• before delete statement trigger

• after delete statement trigger

• before update row trigger

• after update row trigger

• before insert row trigger

• after insert row trigger

• before delete row trigger

• after delete row trigger

This high number of database triggers you can attach to your tables should suffice for all but the most trigger-intensive applications. Given the number of options within Oracle to limit your trigger use, there won’t be a need for maybe three or four triggers on each table in your database. However, if yours is the exception, you can design triggers for each category that fire for multiple triggering events, such insert or update or delete.

One situation to beware of is when the PL/SQL in your trigger that causes other triggers to fire. Although the cascading effect of trigger firing isn’t a problem by itself necessarily, you must ensure the cascading trigger firing does not come back to haunt your table. For example, say trigger A on table A fires on insert. Trigger A inserts a row into table B, which in turn fires an insert trigger on table B, which inserts a row back into table A. As you may imagine, this will cause a problem, and is quite simply a poor design choice. Up to 32 cascading triggers can fire, subject to limitation by setting the MAX_OPEN_CURSORS initialization parameter on Oracle database startup.

Certain restrictions on using datatypes in triggers exist as well. For example, in Oracle7 the use of triggers to modify or populate columns declared to be datatype LONG or LONG RAW can get a little annoying. Triggers can insert data into a column declared as type LONG. Your variables in the trigger, however, cannot be declared as type LONG or LONG RAW. If you want to select data from a column declared as type LONG or LONG RAW, you have to convert your column to a constrained type such as VARCHAR2 as part of the select, and it cannot be longer than 32 kilobytes. Also, you cannot use new or old references on the column of a table declared as type LONG or LONG RAW.

Use of procedures or functions in packages can cause problems when called from a trigger related to the cascading issue described earlier. Sometimes, when an update in a package program unit on the table whose firing trigger has called the package program unit, the two updates may conflict. This conflict can cause the before trigger’s activity to rollback and re-fire several times before the conflict can be resolved. Obviously, this is a performance issue that can cause many problems that must be addressed in system design.

Row evaluation order may also be a factor when database triggers fire. Be aware that a trigger will not necessarily process row data in the order you want it to. Relational databases are not designed to enforce row order. Should you want to process data based on order, you may want to consider writing a stored procedure that uses a cursor with an order by statement after the fact.

Triggers also run into big problems when you don’t handle exceptions and errors that may occur as part of their PL/SQL execution. Remember, triggers are just like other blocks of PL/SQL, so they can have exception handlers. And like other PL/SQL blocks, triggers are going to blow off if an unhandled exception occurs as the trigger executes, causing the activity happening in your trigger to rollback, and returning ugly errors to your users. But, on the bright side, your trigger’s transaction processing rolls back, so you won’t have data corruption issues.

Sometimes, triggers have errors in their execution due to mutating or constraining tables. A table mutates every time an update, insert, or delete statement is issued against it. A table constrains if SQL statements or referential integrity constraints must read data from it in order for a data change to happen to another table. Statement triggers never have problems due to mutating or constraining tables, but row triggers might in certain cases. A row trigger cannot read or modify data from the table it is attached to when firing except though referencing old and new values for that single row. In addition, the trigger cannot modify the table to which a changing column references via primary, foreign, or unique constraint. In other words, if you have an insert row trigger firing on an added record, and a column in that added record has a foreign key, the row trigger cannot go out to the referenced table and add a row to it to satisfy the foreign key constraint. The golden rule in this situation is that the trigger must be bound both by existing read-consistencies and referential integrity. Figure 10-2 demonstrates the restrictions on row triggers related to mutating or constraining tables.

Security on trigger creation is managed with the create trigger privilege. Once created, you will own the trigger. If, on the other hand, you want to put the trigger in someone else’s schema, you must have the create any trigger system privilege. However, trigger creation is one situation where the restrictions don’t stop with mere privilege granting. In addition to possession of the create trigger privilege, you must have one of the following situations in your favor as well. They are:

• Ownership of the table to which the trigger is attached
• Appropriate privileges to issue alter table statements against table to which trigger is attached
• The alter any table system privilege.

Once created, the trigger will be enabled and ready for use. There are no specific privileges required to run a trigger, other than the privileges required to execute the triggering event, update if it is an update trigger, and so on. No special syntax to run the trigger, either, simply execute the triggering event and the trigger fires automatically.

Sometimes a situation will arise where you want to get rid of a trigger without actually getting rid of it. Say you want to execute a batch of update statements on a table with a trigger that maintains historical data for that table. You may want to turn the trigger off or disable it before running the event that would otherwise fire the trigger. To do this, you must issue an alter trigger disable statement. The following code block shows you how to disable our trigger on the EXCH_RATE table from SQL*Plus.

SQL> alter trigger RATE_HIST_TRIGGER_01 disable;

The nice thing about disabling triggers is that you can get them back in action easily by re-enabling them. Disabling simply means the trigger won’t fire; the compiled version of it still lives in the Oracle database. You may want to eliminate the trigger entirely, in which case you issue the drop trigger statement, such as the one listed in the following code block. The downside to dropping a trigger is that after the trigger is dropped, you must fully recreate it before it is available on the database again. The following code block demonstrates enabling and dropping triggers.

SQL> alter trigger RATE_HIST_TRIGGER_01 enable;
SQL> drop trigger RATE_HIST_TRIGGER_01;

Consider the following facts about statement and row trigger. A trigger fires as the result of a triggering event. You can specify the trigger to fire either before or after Oracle processes the triggering statement. You know this feature is set either by clicking on the radio button within Procedure Builder when creating the trigger or with the before or after keywords in the create trigger statement.

The before trigger fires in the following way. When a triggering statement occurs, Oracle processes the trigger first, along with its data changes, if any. Then, Oracle processes the triggering statement’s data changes. As discussed, it is slightly less efficient to process the trigger before the triggering event for update row triggers, but in some cases it might work out better logically, such as in the case of delete triggers. However, there is no significant performance difference between execution of the trigger before or after the triggering event for statement triggers.

In contrast, the after trigger fires after Oracle completes processing for the triggering event. This method is slightly more efficient for update row triggers, as was discussed in the previous section. If you make the trigger wait and fire after the triggering event, then Oracle will already have the changed row stored in the buffer cache of the SGA, thus making it more available for the trigger processing.

One situation you must consider, however, is when triggers won’t fire. Say, for example, you have a table with 5 million rows. To delete all data from this table with a delete from tbl_name statement will take a long time for Oracle to process. Furthermore, your statement may fail due to a lack of space in the rollback segment, thus rolling back your transaction. Instead, you may want to consider the truncate table tbl_name. Now, this statement will run a lot faster and is more efficient with disk space that the comparable delete statement. But, the truncate table statement is a DDL operation, not a DML statement like delete. DDL statements do not fire triggers. Thus, if you have some delete trigger out there and you truncate your table, figure on creating a stored procedure to run the operation before truncating the table.

On some weaker databases in the market, triggers can be set up to perform several different types of activities that may be data-driven. For example, you may want to monitor or audit the update statements on a particular table to ensure that the users who change data are tracked. In addition, you may want to enforce a complex integrity constraint where the constraining data or table depends on the data being added to the database. Other examples include history tables and event logging. Unlike some of those weaker databases out there, Oracle allows you to perform many of these tasks with database features other than triggers. Features such as database auditing, declarative integrity constraints built into tables, and advanced replication with snapshots avoid some of the performance burdens associated with firing the substantial number of triggers inherent in a system that uses triggers to perform these tasks. Still, you should consider how triggers can be used to mimic functions on the Oracle database, and identify when triggers may handle functionality that other Oracle features do not.

The one type of activity where a trigger really shines is for creating data in one table based on a triggering event in another. Because this is one area where OCP will test you extensively, you should understand it thoroughly. Figure 10-3 shows pictorially the situation where you have an insert or other triggering statement that happens on the database, and you want to populate another table with some sort of information.

Let’s say you have a table called SOCCER_FANS that keeps track of all the soccer fans in the world. In each you’re converting to this format from a format where you had several soccer fans tables, one for each country in the world, like SOCCER_FANS_US, SOCCER_FANS_CAN, etc. To move the data into your SOCCER_FANS table, you might use a insert into SOCCER_FANS (select * from SOCCER_FANS_CAN) statement, substituting the name of each soccer fans table for each country in turn.

Let’s assume you have a companion table for SOCCER_FANS in your new application that tracks each fan’s snack choices to help the stadium concessions determine what to get for each soccer fan as they enter the stadium. However, this update will move many rows into the table at a time. If you use a statement trigger, the trigger fires only once per update, while the row trigger will fire for each row moved. Obviously, you want to use a row trigger. Assuming that your populated table is called FAN_SNACK_CHOICES with columns FAN_ID, PREF_BVG1, PREF_BVG2, PREF_FOOD1 and PREF_FOOD2, your trigger might look something like the following code block.

CREATE OR REPLACE TRIGGER stacy.soccer_fan_snack_01
AFTER insert ON soccer_fans
FOR EACH ROW
BEGIN
INSERT INTO fan_snack_choices (fan_id, pref_bvg1, pref_bvg2,
pref_food1, pref_food2)
VALUES (:new.fan_id,‘BEER’,‘SODA’,‘HAMBURGER’,‘ICE CREAM’);
END;

Your ability to pass OCP will depend on your ability to read trigger code, understand what the trigger does, and figure out if that’s what you want to do. For example, if you eliminate the for each row line, your trigger will not fire once per row, instead it will fire once for the entire insert statement. You should also understand the whole mutating/constraining table issue as it relates to row triggers. First of all, mutating or constraining table issues don’t relate to statement triggers, only row triggers. With this fact in mind, recall that within your row trigger you may not read or alter data on the table to which the trigger is attached, save for using the old and new keywords to reference the old version of the row data and the new. So, with your SOCCER_FAN_SNACK_01 triggery you cannot select data from SOCCER_FAN outright, only reference new SOCCER_FAN row data using :new.fan_id.

Another important restriction relates to foreign keys. Say for example your FAN_SNACK_CHOICES table has a foreign key constraint on its FAN_ID column that references back to FAN_ID on SOCCER_FANS, to ensure that snacks aren’t added for which there are no corresponding soccer fans. When the row trigger fires, however, you receive an error saying something to the effect of "Your table is mutating, foreign key constraint may not see it." To correct this type of problem, drop the foreign key constraint, and SOCCER_FAN_SNACKS will not reference the SOCCER_FANS table on FAN_ID anymore.

Another situation where using triggers complements the Oracle database rather than mimicking existing other functionality is where you want to populate a history table. An application idea has already been given, with the EXCH_RATE table change and related historical data population. Some issues to watch in that situation include the mutating/constraining table issue raised in the previous example, the selection of data from the table to which the row trigger is attached, and so on.

This chapter, though slightly more brief, is highly important to understand in order to pass OCP exam 2. On the OCP exam, you will find about 24% of the test questions relate to usage of triggers. This chapter covers trigger usage, development of triggers, and management of both triggers and the issues that arise with their usage. The term trigger can be used to refer to many different objects around the Oracle database. For example, there are triggers within the Oracle database and triggers on Oracle Forms. There can be some blurring between triggers and other programs in Oracle that use PL/SQL code in them, too, like stored procedures, functions, and the like. To identify a database trigger, remember that the database trigger is stored within the database, unlike a Forms trigger, which is stored in the Forms application. And although stored procedures and functions also contain PL/SQL code, only the database trigger has a defined process set up for executing the PL/SQL code already defined.

The first key to passing OCP is your ability to identify the main two types of database triggers. There are statement triggers and row triggers. A statement trigger will fire when a triggering event occurs on the table, such as update, insert, or delete. It will fire once for that event and once only. A statement trigger is created with the basic create or replace trigger syntax in which the second line defines your triggering event, and after that you define the PL/SQL code that will comprise the body of your database trigger. Statement triggers do not allow you to access data being changed as part of the triggering event, they may be used for simple purposes of counting the times a triggering statement occurs. Before taking OCP exam 2, be sure you can identify a statement trigger based on looking at the trigger source code.

You name your triggers explicitly in Oracle, although each trigger needn’t have a unique name. For example, if you have table FOO and you want to create a trigger called FOO, you can do that, although it may become confusing later when you have many triggers and many tables, and they all have the same names. Procedure Builder also generates unique names for your triggers automatically, but this is an application feature, not a database feature.

Row triggers are more flexible than statement triggers because row triggers can fire for every row processed by a triggering statement. In addition to the create or replace trigger statement and the line defining the triggering statement, the row trigger adds syntax to define how to access old and new column values for the individual row affected by the triggering statement. Also, the line for each row must be added to explicitly define this as a row trigger. Now, you need to make sure you can identify a row trigger when given a sample trigger source code statement. Specifically, you should make sure you understand that you cannot reference row data if the for each row line is not included to define the trigger as a row trigger.

A triggering statement is an update, insert or delete statement on the database to which the trigger is attached, that causes the trigger to fire. Oracle database triggers can fire either before the triggering event is processed or after, depending on how you code the trigger. The before keyword is used to specify the trigger process its thing before Oracle processes the triggering statement, as in before insert on DATADB. The after keyword is used to specify the trigger process after Oracle processes the triggering statement, as in after delete on DATADB.

Assuming you are using the standard old and new keywords for referencing old and new column values for rows as part of the row trigger, there is some special syntax for you to remember when writing code to reference that data. Think of old and new as being records defined to be the same rowtype as the table on which the row trigger is defined. You then reference a particular column in the following way, either :old.col_name for old row values or :new.col_name for new ones. Do NOT forget the colon preceding old or new in your trigger code body or else it will not compile. Use common sense when referencing old and new values, for example there won’t be an old value for an insert row trigger, nor will there be a new value for a delete trigger.

For all but the most obscure information about triggers, you should be able to find the information you need in the USER_, ALL_, or DBA_TRIGGERS dictionary views in the Oracle database. The columns in the DBA_TRIGGERS views include OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, TABLE_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, and TRIGGER_BODY. Important to note is that you can find out the status of your trigger (ie, enabled or disabled) in the STATUS column on DBA_TRIGGERS, not on the DBA_OBJECTS view like other database objects in Oracle. In addition, there is information in the DBA_TRIGGER_COLS column on the table columns in use within the trigger.

You are allowed to define up to 12 triggers on a table. Bear in mind, too, that you can specify multiple triggering events in the second line of your trigger creation code, as in after insert or update on DATADB. Within your trigger code body, you can specify certain code blocks to run for each triggering statement with the use of special keywords inserting, updating, or deleting, which evaluate to TRUE or FALSE based on whether or not the triggering event is an insert, update, or delete statement, respectively. These conditional predicates are defined in the DBMS_STANDARD package.

Your twelve triggers on the table fall into the following categories. There can be six each of triggers that process before and after the triggering statement. Six triggers can be statement triggers and the other six can be row triggers. Within each of those categories, you can have three types of triggering events, update, insert, or delete. Given the fact that many activities you may have chosen to develop triggers for have specific Oracle features to address them, such as database auditing, replication, and other activities, you shouldn’t usually exceed this limit, and very well may not even come close to it.

The following restrictions are present on database triggers. All variables and parameters passed to stored procedures in Oracle must have internal datatypes like DATE, NUMBER, RAW, LONG, LONG RAW, VARCHAR2, CHAR, or ROWID. No PL/SQL datatype extensions like PLS_INTEGER, POSITIVE, TEXT, or the like will be accepted. Related to this datatype limitation is another on columns of type LONG or LONG RAW. To select data from a column of type LONG within your trigger, you must convert it to a constrained datatype like VARCHAR2, and limit its size to 32K or less. In row triggers, you cannot reference columns of type LONG or LONG RAW using old or new. Another limit on triggers relates to other triggers that may fire when one trigger fires. This effect is called cascading. A trigger may cause up to 32 cascading triggers to fire, after which Oracle returns with an error.

You should take care to handle all errors that may arise as the result of your trigger. You can code an exception handler into the PL/SQL block appearing as part of your trigger body that handles your errors. If you have an unhandled exception in your trigger body and Oracle raises it, both the work of your triggering statement and the trigger will be rolled back.

You must also factor in the effects of mutating or constraining tables as part of your trigger execution. Any DML statement such as an update, insert, or delete will cause a table to mutate. Similarly, any foreign key constraint or select lookup on another table containing valid values is a constraining table. If a row trigger performs a select statement on either the table it is attached to or another table that references the table the trigger is attached to via foreign key, the trigger may encounter mutating table errors. This problem is due to the fact that the trigger won’t be able to see the change being made until the action is complete. In some cases, you simply cannot fix the problem other than not to select data from the table your trigger attaches to. If instead you are trying to select data from the table to which your trigger attaches, you should consider dropping the foreign key constraint if you want your trigger to work, or alternately you should enforce the foreign key constraint using a trigger rather than a declarative foreign key constraint.

To use a trigger, you must have appropriate permissions to run the triggering statement that causes the trigger to fire. There is no execute permission on triggers per se, other than this permission. Creating a trigger requires the create trigger privilege, along with one of the following: ownership of the table to which you associate the trigger, privileges to issue alter table statements against the table to which you associate the trigger, or the alter any table system privilege. A created trigger is enabled automatically. To disable it, issue alter trigger trig_name disable. Disabling a trigger causes it not to fire but keeps its definition in the database. To eliminate the trigger entirely, issue the drop trigger statement.

Before taking OCP exam 2, make sure you understand situations where triggers complement your use of the Oracle database. Most of the time, there is another feature in Oracle that substitutes for triggers very well, however, when it comes to populating other tables based on triggering statements on one table, triggers can’t be beat. But wholesale replication is better accomplished with Oracle’s replication features like snapshots.

• Any trigger definition that doesn’t contain these distinguishing characteristics of row triggers is a statement trigger.
• Triggers can be set to fire either before Oracle processes the triggering statement with the use of the before keyword in a trigger definition, as in before delete on EMPL, or after Oracle processes the triggering statement, as in after insert on EMPL.
• Triggers that fire as a result of update statements can be set to fire only when a specific column is changed, with use of after update of col_name on EMPL syntax. If an update statement occurs that doesn’t change the column specified, the trigger does not fire.
• You can define multiple triggering events for one trigger, and then distinguish the processing of the trigger with an if-then-else statement within the trigger body.
• The keywords inserting, updating, or deleting can be used when multiple triggering events are defined to use in the if-then-else statement. These three keywords evaluate to TRUE or FALSE depending on what statement has fired the trigger.
• Any valid PL/SQL statement is acceptable for use in a trigger.
• Row triggers fire for every row that is affected by the triggering statement. You can, however, include a when clause into the definition of the trigger.
• The when clause is a SQL statement that evaluates to TRUE or FALSE. If the when clause evaluates to TRUE, the trigger will fire for that row.
• A when clause that evaluates to FALSE causes the trigger not to fire for that row. This does not prevent the row trigger from firing for another row.
• Basically all information set about a trigger, including status, when clause, trigger source code, table to which the trigger is attached, and other things, can be found in the DBA_, ALL_ and USER_TRIGGERS dictionary view in Oracle.
• Up to 12 triggers can be associated with an Oracle table, or one trigger of any combination of the following features: before or after, statement or row, update or insert or delete.
• A trigger whose activities causes other triggers to fire is called a cascading trigger. Up to 32 cascades are permitted to fire as the result of one trigger, after that Oracle returns an error and rolls back all changes.
• You are not permitted to reference LONG and LONG RAW columns using old and new syntax in your row trigger.
• You cannot select data from LONG or LONG RAW columns in your row trigger without converting the values to constrained datatypes like VARCHAR2. Even then, you’re limited to seeing only 32K of the LONG or LONG RAW columns.
• Row triggers are limited by the effects of mutating or constraining tables. A mutating table is one that is affected by the triggering event. A constraining table is one containing valid values referenced by a column in the table affected by the trigger.
• You cannot perform a select statement on the table your trigger is attached to from your row trigger.
• You cannot update or insert data into another table if one of the column values you add or modify has a foreign key constraint back to the table your trigger is attached.
• To create a trigger you must have the create trigger privilege. Additionally, you must:
• Own the table associated to the trigger
• Be able to issue alter table statements associated to the trigger
• Have the alter any table privilege

• Once created, the trigger is enabled and ready to execute. Running the trigger requires no special privileges other than permission to use the triggering statement.
• You prevent the trigger from executing by disabling it. This is done with alter trigger trig_name disable. This allows you to stop trigger execution without eliminating the trigger definition from Oracle entirely.
• To eliminate the trigger from Oracle entirely, you issue the drop trigger trig_name statement.
• A truncate table statement will not cause a trigger to fire. Importing data into Oracle with the IMPORT utility will cause triggers to fire.
• Many uses for triggers such as auditing use of insert or delete statements duplicates the effects of other Oracle features. However, one use of triggers that complements the Oracle database is the population or change of data in one table based on a triggering statement executed on another. Such uses include:
• Population of historical data tables or rows in child tables
• Population of data for event tracking purposes

• Its been said before, but you should hear it again – make sure you understand the effects of mutating and constraining tables on row triggers. They affect row trigger execution only, and prevent certain activities you may want the trigger to perform.
• In general, if you’re counting on data changes in the associated table made by the triggering statement to be there in order for the row trigger to work, your row trigger will fail because of mutating or constraining table errors.