Oracle PL/SQL Programming (2014)

Part V. PL/SQL Application Construction

Chapter 19. Triggers

Database triggers are named program units that are executed in response to events that occur in the database. Triggers are critical elements of a well-designed application built on the Oracle database and are used to do the following:

Perform validation on changes being made to tables

Because the validation logic is attached directly to the database object, database triggers offer a strong guarantee that the required logic will always be executed and enforced.

Automate maintenance of the database

Starting with Oracle8i Database, you can use database startup and shutdown triggers to automatically perform necessary initialization and cleanup steps. This is a distinct advantage over creating and running such steps as scripts external to the database.

Apply rules concerning acceptable database administration activity in a granular fashion

You can use triggers to tightly control what kinds of actions are allowed on database objects, such as dropping or altering tables. Again, by putting this logic in triggers, you make it very difficult, if not impossible, for anyone to bypass the rules you have established.

Five different types of events can have trigger code attached to them:

Data Manipulation Language (DML) statements

DML triggers are available to fire whenever a record is inserted into, updated in, or deleted from a table. These triggers can be used to perform validation, set default values, audit changes, and even disallow certain DML operations.

Data Definition Language (DDL) statements

DDL triggers fire whenever DDL is executed—for example, whenever a table is created. These triggers can perform auditing and prevent certain DDL statements from executing.

Database events

Database event triggers fire whenever the database starts up or is shut down, whenever a user logs on or off, and whenever an Oracle error occurs. For Oracle8i Database and above, these triggers provide a means of tracking activity in the database.

INSTEAD OF

INSTEAD OF triggers are essentially alternatives to DML triggers. They fire when inserts, updates, and deletes are about to occur; your code specifies what to do in place of these DML operations. INSTEAD OF triggers control operations on views, not tables. They can be used to make nonupdateable views updateable and to override the behavior of views that are updateable.

Suspended statements

Oracle9i Database introduced the concept of suspended statements. Statements experiencing space problems (lack of tablespace or quotas exceeded) can enter a suspended mode until the space problem is fixed. Triggers can be added to the mix to automatically alert someone of the problem or even to fix it.

This chapter describes these types of triggers; for each, I’ll provide syntax details, example code, and suggested uses. I’ll also touch on trigger maintenance at the end of the chapter.

If you need to emulate triggers on SELECT statements (queries), you should investigate the use of fine-grained auditing (FGA), which is described in Chapter 23 and in greater detail in the book Oracle PL/SQL for DBAs.

DML Triggers

Data Manipulation Language triggers fire when records are inserted into, updated within, or deleted from a particular table, as shown in Figure 19-1. These are the most common types of triggers, especially for developers; the other trigger types are used primarily by DBAs. And starting with Oracle Database 11g, you can combine several DML triggers into one compound trigger.

Before you begin writing your DML trigger, you should answer the following questions:

§ Should the trigger fire once for the entire DML statement or once for each row involved in the statement?

§ Should the trigger fire before or after the statement/row?

§ Should the trigger fire for inserts, updates, deletes, or a combination thereof?

Figure 19-1. DML trigger flow of control

DML Trigger Concepts

Before diving into the syntax and examples, you may find it useful to review these DML trigger concepts and associated terminology:

BEFORE trigger

A trigger that executes before a certain operation occurs, such as BEFORE INSERT.

AFTER trigger

A trigger that executes after a certain operation occurs, such as AFTER UPDATE.

Statement-level trigger

A trigger that executes for a SQL statement as a whole (which may, in turn, affect one or more individual rows in a database table).

Row-level trigger

A trigger that executes for a single row that has been affected by the execution of a SQL statement. Suppose that the books table contains 1,000 rows. Then the following UPDATE statement will modify 1,000 rows:

UPDATE books SET title = UPPER (title);

And if I define a row-level update trigger on the books table, that trigger will fire 1,000 times.

NEW pseudo-record

A data structure named NEW that looks like and (mostly) acts like a PL/SQL record. This pseudo-record is available only within update and insert DML triggers; it contains the values for the affected row after any changes were made.

OLD pseudo-record

A data structure named OLD that looks like and (mostly) acts like a PL/SQL record. This pseudo-record is available only within update and delete DML triggers; it contains the values for the affected row before any changes were made.

WHEN clause

The portion of the DML trigger that is run to determine whether or not the trigger code should be executed (allowing you to avoid unnecessary execution).

DML trigger scripts

To explore some of the concepts presented in the previous section, I have made the scripts in the following table available on the book’s website.

Transaction participation

By default, DML triggers participate in the transaction from which they were fired. This means that:

§ If a trigger terminates with an unhandled exception, then the statement that caused the trigger to fire is rolled back.

§ If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction.

§ You cannot issue a COMMIT or ROLLBACK from within a DML trigger.

NOTE

If you define your DML trigger to be an autonomous transaction (discussed in Chapter 14), however, then any DML performed inside the trigger will be saved or rolled back—with your explicit COMMIT or ROLLBACK statement—without affecting the main transaction.

The following sections present the syntax for creating a DML trigger, provide reference information on various elements of the trigger definition, and explore an example that uses the many components and options for these triggers.

Creating a DML Trigger

To create (or replace) a DML trigger, use the syntax shown here:

1 CREATE [OR REPLACE] TRIGGER trigger_name

2 {BEFORE | AFTER}

3 {INSERT | DELETE | UPDATE | UPDATE OF column_list } ON table_name

4 [FOR EACH ROW]

5 [WHEN (...)]

6 [DECLARE ... ]

7 BEGIN

8 ...executable statements...

9 [EXCEPTION ... ]

10 END [trigger_name];

The following table provides an explanation of these different elements.

Here are a few examples of DML trigger usage:

§ I want to make sure that whenever an employee is added or changed, all necessary validation is run. Notice that I pass the necessary fields of the NEW pseudo-record to individual check routines in this row-level trigger:

§ TRIGGER validate_employee_changes

§ AFTER INSERT OR UPDATE

§ ON employees

§ FOR EACH ROW

§ BEGIN

§ check_date (:NEW.hire_date);

§ check_email (:NEW.email);

END;

§ The following BEFORE INSERT trigger captures audit information for the CEO compensation table. It also relies on the autonomous transaction feature to commit this new row without affecting the “outer” or main transaction:

§ TRIGGER bef_ins_ceo_comp

§ BEFORE INSERT

§ ON ceo_compensation

§ FOR EACH ROW

§ DECLARE

§ PRAGMA AUTONOMOUS_TRANSACTION;

§ BEGIN

§ INSERT INTO ceo_comp_history

§ VALUES (:NEW.name,

§ :OLD.compensation, :NEW.compensation,

§ 'AFTER INSERT', SYSDATE);

§ COMMIT;

END;

The WHEN clause

Use the WHEN clause to fine-tune the situations under which the body of the trigger code will actually execute. In the following example, I use the WHEN clause to make sure that the trigger code does not execute unless the new salary is changing to a different value:

TRIGGER check_raise

AFTER UPDATE OF salary

ON employees

FOR EACH ROW

WHEN ((OLD.salary != NEW.salary) OR

(OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR

(OLD.salary IS NOT NULL AND NEW.salary IS NULL))

BEGIN

...

In other words, if a user issues an UPDATE to a row and for some reason sets the salary to its current value, the trigger will and must fire, but the reality is that you really don’t need any of the PL/SQL code in the body of the trigger to execute. By checking this condition in the WHEN clause, you avoid some of the overhead of starting up the PL/SQL block associated with the trigger.

NOTE

The genwhen.sp file on the book’s website offers a procedure that will generate a WHEN clause to ensure that the new value is actually different from the old.

In most cases, you will reference fields in the OLD and NEW pseudo-records in the WHEN clause, as in the example just shown. You may also, however, write code that invokes built-in functions, as in the following WHEN clause that uses SYSDATE to restrict the INSERT trigger to fire only between 9 a.m. and 5 p.m.:

TRIGGER valid_when_clause

BEFORE INSERT ON frame

FOR EACH ROW

WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )

...

Here are some things to keep in mind when using the WHEN clause:

§ Enclose the entire logical expression inside parentheses. These parentheses are optional in an IF statement, but required in the trigger WHEN clause.

§ Do not include the “:” in front of the OLD and NEW names. This colon (indicating a host variable) is required in the body of the trigger PL/SQL code, but cannot be used in the WHEN clause.

§ You can invoke SQL built-in functions only from within the WHEN clause; you will not be able to call user-defined functions or functions defined in built-in packages (such as DBMS_UTILITY). Attempts to do so will generate an ORA-04076: invalid NEW or OLD specification error. If you need to invoke such functions, move that logic to the beginning of the trigger execution section.

NOTE

The WHEN clause can be used only with row-level triggers. You will get a compilation error (ORA-04077) if you try to use it with statement-level triggers.

Working with NEW and OLD pseudo-records

Whenever a row-level trigger fires, the PL/SQL runtime engine creates and populates two data structures that function much like records. They are the NEW and OLD pseudo-records (“pseudo” because they don’t share all the properties of real PL/SQL records). OLD stores the original values of the record being processed by the trigger; NEW contains the new values. These records have the same structure as a record declared using %ROWTYPE on the table to which the trigger is attached.

Here are some rules to keep in mind when working with NEW and OLD:

§ With triggers on INSERT operations, the OLD structure does not contain any data; there is no “old” set of values.

§ With triggers on UPDATE operations, both the OLD and NEW structures are populated. OLD contains the values prior to the update; NEW contains the values the row will contain after the update is performed.

§ With triggers on DELETE operations, the NEW structure does not contain any data; the record is about to be erased.

§ The NEW and OLD pseudo-records also contain the ROWID pseudocolumn; this value is populated in both OLD and NEW with the same value, in all circumstances. Go figure!

§ You cannot change the field values of the OLD structure; attempting to do so will raise the ORA-04085 error. You can modify the field values of the NEW structure.

§ You can’t pass a NEW or OLD structure as a “record parameter” to a procedure or function called within the trigger. You can pass only individual fields of the pseudo-record. See the gentrigrec.sp script on the book’s website for a program that will generate code transferring NEW and OLD values to records that can be passed as parameters.

§ When referencing the NEW and OLD structures within the anonymous block for the trigger, you must preface those keywords with a colon, as in:

IF :NEW.salary > 10000 THEN...

§ You cannot perform record-level operations with the NEW and OLD structures. For example, the following statement will cause the trigger compilation to fail:

BEGIN :new := NULL; END;

You can also use the REFERENCING clause to change the names of the pseudo-records within the database trigger; this allows you to write code that is more self-documenting and application-specific. Here is one example:

/* File on web: full_old_and_new.sql */

TRIGGER audit_update

AFTER UPDATE

ON frame

REFERENCING OLD AS prior_to_cheat NEW AS after_cheat

FOR EACH ROW

BEGIN

INSERT INTO frame_audit

(bowler_id,

game_id,

old_score,

new_score,

change_date,

operation)

VALUES (:after_cheat.bowler_id,

:after_cheat.game_id,

:prior_to_cheat.score,

:after_cheat.score,

SYSDATE,

'UPDATE');

END;

Run the full_old_and_new.sql script available on the book’s website to take a look at the behavior of the OLD and NEW pseudo-records.

Determining the DML action within a trigger

Oracle provides a set of functions (also known as operational directives) that allow you to determine which DML action caused the firing of the current trigger. Each of these functions returns TRUE or FALSE, as described next:

INSERTING

Returns TRUE if the trigger was fired by an insert into the table to which the trigger is attached, and FALSE if not.

UPDATING

Returns TRUE if the trigger was fired by an update of the table to which the trigger is attached, and FALSE if not.

DELETING

Returns TRUE if the trigger was fired by a delete from the table to which the trigger is attached, and FALSE if not.

Using these directives, it’s possible to create a single trigger that consolidates the actions required for each different type of operation. Here’s one such trigger:

/* File on web: one_trigger_does_it_all.sql */

TRIGGER three_for_the_price_of_one

BEFORE DELETE OR INSERT OR UPDATE ON account_transaction

FOR EACH ROW

BEGIN

-- track who created the new row

IF INSERTING

THEN

:NEW.created_by := USER;

:NEW.created_date := SYSDATE;

-- track deletion with special audit program

ELSIF DELETING

THEN

audit_deletion(USER,SYSDATE);

-- track who last updated the row

ELSIF UPDATING

THEN

:NEW.UPDATED_BY := USER;

:NEW.UPDATED_DATE := SYSDATE;

END IF;

END;

The UPDATING function is overloaded with a version that takes a specific column name as an argument. This is handy for isolating specific column updates:

/* File on web: overloaded_update.sql */

TRIGGER validate_update

BEFORE UPDATE ON account_transaction

FOR EACH ROW

BEGIN

IF UPDATING ('ACCOUNT_NO')

THEN

errpkg.raise('Account number cannot be updated');

END IF;

END;

Specification of the column name is not case sensitive. The name is not evaluated until the trigger executes, and if the column does not exist in the table to which the trigger is attached, it will evaluate to FALSE.

NOTE

Operational directives can be called from within any PL/SQL block, not just triggers. They will, however, only evaluate to TRUE within a DML trigger or code called from within a DML trigger.

DML Trigger Example: No Cheating Allowed!

One application function for which triggers are perfect is change auditing. Consider the example of Paranoid Pam (or Ms. Trustful, as we call her), who runs a bowling alley and has been receiving complaints about people cheating on their scores. She recently implemented a complete Oracle application known as Pam’s Bowlerama Scoring System, and now wants to augment it to catch the cheaters.

The focal point of Pam’s application is the frame table that records the score of a particular frame of a particular game for a particular player:

/* File on web: bowlerama_tables.sql */

TABLE frame

(bowler_id NUMBER,

game_id NUMBER,

frame_number NUMBER,

strike VARCHAR2(1) DEFAULT 'N',

spare VARCHAR2(1) DEFAULT 'N',

score NUMBER,

CONSTRAINT frame_pk

PRIMARY KEY (bowler_id, game_id, frame_number))

Pam enhances the frame table with an audit version to catch all before and after values, so that she can compare them and identify fraudulent activity:

TABLE frame_audit

(bowler_id NUMBER,

game_id NUMBER,

frame_number NUMBER,

old_strike VARCHAR2(1),

new_strike VARCHAR2(1),

old_spare VARCHAR2(1),

new_spare VARCHAR2(1),

old_score NUMBER,

new_score NUMBER,

change_date DATE,

operation VARCHAR2(6))

For every change to the frame table, Pam would like to keep track of before and after images of the affected rows. So she creates the following simple audit trigger:

/* File on web: bowlerama_full_audit.sql */

1 TRIGGER audit_frames

2 AFTER INSERT OR UPDATE OR DELETE ON frame

3 FOR EACH ROW

4 BEGIN

5 IF INSERTING THEN

6 INSERT INTO frame_audit(bowler_id,game_id,frame_number,

7 new_strike,new_spare,new_score,

8 change_date,operation)

9 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,

10 :NEW.strike,:NEW.spare,:NEW.score,

11 SYSDATE,'INSERT');

12

13 ELSIF UPDATING THEN

14 INSERT INTO frame_audit(bowler_id,game_id,frame_number,

15 old_strike,new_strike,

16 old_spare,new_spare,

17 old_score,new_score,

18 change_date,operation)

19 VALUES(:NEW.bowler_id,:NEW.game_id,:NEW.frame_number,

20 :OLD.strike,:NEW.strike,

21 :OLD.spare,:NEW.spare,

22 :OLD.score,:NEW.score,

23 SYSDATE,'UPDATE');

24

25 ELSIF DELETING THEN

26 INSERT INTO frame_audit(bowler_id,game_id,frame_number,

27 old_strike,old_spare,old_score,

28 change_date,operation)

29 VALUES(:OLD.bowler_id,:OLD.game_id,:OLD.frame_number,

30 :OLD.strike,:OLD.spare,:OLD.score,

31 SYSDATE,'DELETE');

32 END IF;

33 END audit_frames;

Notice that for the INSERTING clause (lines 6–11), she relies on the NEW pseudo-record to populate the audit row. For UPDATING (lines 14–23), a combination of NEW and OLD information is used. For DELETING (lines 26–31), Pam has only OLD information with which to work. With this trigger in place, Pam can sit back and wait for action.

Of course, Pam doesn’t announce her new auditing system. In particular, Sally Johnson (a very ambitious but not terribly skilled bowler) has no idea she is being watched. Sally has decided that she really wants to be the champion this year, and will stop at nothing to make it happen. Her father owns the bowling alley, she has access to SQL*Plus, and she knows that her bowler ID is 1. All that constitutes enough privilege and information to allow her to bypass the application GUI altogether, connect directly into SQL*Plus, and work some very unprincipled “magic.”

Sally starts out by giving herself a strike in the first frame:

SQL> INSERT INTO frame

2 (BOWLER_ID,GAME_ID,FRAME_NUMBER,STRIKE)

3 VALUES(1,1,1,'Y');

1 row created.

But then she decides to be clever. She immediately downgrades her first frame to a spare to be less conspicuous:

SQL> UPDATE frame

2 SET strike = 'N',

3 spare = 'Y'

4 WHERE bowler_id = 1

5 AND game_id = 1

6 AND frame_number = 1;

1 row updated.

Uh oh! Sally hears a noise in the corridor. She loses her nerve and tries to cover her tracks:

SQL> DELETE frame

2 WHERE bowler_id = 1

3 AND game_id = 1

4 AND frame_number = 1;

1 row deleted.

SQL> COMMIT;

Commit complete.

She even verifies that her entries were deleted:

SQL> SELECT * FROM frame;

no rows selected

Wiping the sweat from her brow, Sally signs out, but vows to come back later and follow through on her plans.

Ever suspicious, Pam signs in and quickly discovers what Sally was up to by querying the audit table (Pam might also consider setting up an hourly job via DBMS_JOB to automate this part of the auditing procedure):

SELECT bowler_id, game_id, frame_number

, old_strike, new_strike

, old_spare, new_spare

, change_date, operation

FROM frame_audit

Here is the output:

BOWLER_ID GAME_ID FRAME_NUMBER O N O N CHANGE_DA OPERAT

--------- ------- ------------ - - - - --------- ------

1 1 1 Y N 12-SEP-00 INSERT

1 1 1 Y N N Y 12-SEP-00 UPDATE

1 1 1 N N 12-SEP-00 DELETE

Sally is so busted! The audit entries show what Sally was up to even though no changes remain behind in the frame table. All three statements were audited by Pam’s DML trigger: the initial insert of a strike entry, the downgrade to a spare, and the subsequent removal of the record.

Applying the WHEN clause

After using her auditing system for many successful months, Pam undertakes an effort to further isolate potential problems. She reviews her application frontend and determines that the strike, spare, and score fields are the only ones that can be changed. Thus, her trigger can be more specific:

TRIGGER audit_update

AFTER UPDATE OF strike, spare, score

ON frame

REFERENCING OLD AS prior_to_cheat NEW AS after_cheat

FOR EACH ROW

BEGIN

INSERT INTO frame_audit (...)

VALUES (...);

END;

After a few weeks of this implementation, Pam is still not happy with the auditing situation because audit entries are being created even when values are set equal to themselves. Updates like this one are producing useless audit records that show nothing changing:

SQL> UPDATE FRAME

2 SET strike = strike;

1 row updated.

SQL> SELECT old_strike,

2 new_strike,

3 old_spare,

4 new_spare,

5 old_score,

6 new_score

7 FROM frame_audit;

O N O N OLD_SCORE NEW_SCORE

- - - - ---------- ----------

Y Y N N

Pam needs to further isolate the trigger so that it fires only when values actually change. She does this using the WHEN clause shown here:

/* File on web: final_audit.sql */

TRIGGER audit_update

AFTER UPDATE OF STRIKE, SPARE, SCORE ON FRAME

REFERENCING OLD AS prior_to_cheat NEW AS after_cheat

FOR EACH ROW

WHEN ( prior_to_cheat.strike != after_cheat.strike OR

prior_to_cheat.spare != after_cheat.spare OR

prior_to_cheat.score != after_cheat.score )

BEGIN

INSERT INTO FRAME_AUDIT ( ... )

VALUES ( ... );

END;

Now entries will appear in the audit table only if something did indeed change, allowing Pam to quickly identify possible cheaters. Pam performs a quick final test of her trigger:

SQL> UPDATE frame

2 SET strike = strike;

1 row updated.

SQL> SELECT old_strike,

2 new_strike,

3 old_spare,

4 new_spare,

5 old_score,

6 new_score

7 FROM frame_audit;

no rows selected

Using pseudo-records to fine-tune trigger execution

Pam has implemented an acceptable level of auditing in her system; now she’d like to make it a little more user-friendly. Her most obvious idea is to have her system add 10 to the score for frames recording a strike or spare. This allows the scorekeeper to track only the score for subsequent bowls while the system adds the strike score:

/* File on web: set_score.sql */

TRIGGER set_score

BEFORE INSERT ON frame

FOR EACH ROW

WHEN ( NEW.score IS NOT NULL )

BEGIN

IF :NEW.strike = 'Y' OR :NEW.spare = 'Y'

THEN

:NEW.score := :NEW.score + 10;

END IF;

END;

NOTE

Remember that field values in the NEW records can be changed only in BEFORE row triggers.

Being a stickler for rules, Pam decides to add score validation to her set of triggers:

/* File on web: validate_score.sql */

TRIGGER validate_score

AFTER INSERT OR UPDATE

ON frame

FOR EACH ROW

BEGIN

IF :NEW.strike = 'Y' AND :NEW.score < 10

THEN

RAISE_APPLICATION_ERROR (

−20001,

'ERROR: Score For Strike Must Be >= 10'

);

ELSIF :NEW.spare = 'Y' AND :NEW.score < 10

THEN

RAISE_APPLICATION_ERROR (

−20001,

'ERROR: Score For Spare Must Be >= 10'

);

ELSIF :NEW.strike = 'Y' AND :NEW.spare = 'Y'

THEN

RAISE_APPLICATION_ERROR (

−20001,

'ERROR: Cannot Enter Spare And Strike'

);

END IF;

END;

Now when there is any attempt to insert a row that violates this condition, it will be rejected:

SQL> INSERT INTO frame VALUES(1,1,1,'Y',NULL,5);

2 INSERT INTO frame

*

ERROR at line 1:

ORA-20001: ERROR: Score For Strike Must >= 10

Multiple Triggers of the Same Type

Above and beyond all of the options presented for DML triggers, it is also possible to have multiple triggers of the same type attached to a single table. Switching from bowling to golf, consider the following example that provides a simple commentary on a golf score by determining its relationship to a par score of 72.

A single row-level BEFORE INSERT trigger would suffice:

/* File on web: golf_commentary.sql */

TRIGGER golf_commentary

BEFORE INSERT

ON golf_scores

FOR EACH ROW

DECLARE

c_par_score CONSTANT PLS_INTEGER := 72;

BEGIN

:new.commentary :=

CASE

WHEN :new.score < c_par_score THEN 'Under'

WHEN :new.score = c_par_score THEN NULL

ELSE 'Over' END || ' Par'

END;

However, the requirement could also be satisfied with three separate row-level BEFORE INSERT triggers with mutually exclusive WHEN clauses:

TRIGGER golf_commentary_under_par

BEFORE INSERT ON golf_scores

FOR EACH ROW

WHEN (NEW.score < 72)

BEGIN

:NEW.commentary := 'Under Par';

END;

TRIGGER golf_commentary_par

BEFORE INSERT ON golf_scores

FOR EACH ROW

WHEN (NEW.score = 72)

BEGIN

:NEW.commentary := 'Par';

END;

TRIGGER golf_commentary_over_par

BEFORE INSERT ON golf_scores

FOR EACH ROW

WHEN (NEW.score > 72)

BEGIN

:NEW.commentary := 'Over Par';

END;

Both implementations are perfectly acceptable and have advantages and disadvantages. A single trigger is easier to maintain because all of the code is in one place, while separate triggers reduce parse and execution time when more complex processing is required.

Who Follows Whom

Prior to Oracle Database 11g, there was no way to guarantee the order in which multiple DML triggers would fire. While this is not a concern in the previous example, it could be a problem in others, as shown in the next example.

What values will be shown by the final query?

/* File on web: multiple_trigger_seq.sql */

TABLE incremented_values

(value_inserted NUMBER,

value_incremented NUMBER);

TRIGGER increment_by_one

BEFORE INSERT ON incremented_values

FOR EACH ROW

BEGIN

:NEW.value_incremented := :NEW.value_incremented + 1;

END;

TRIGGER increment_by_two

BEFORE INSERT ON incremented_values

FOR EACH ROW

BEGIN

IF :NEW.value_incremented > 1 THEN

:NEW.value_incremented := :NEW.value_incremented + 2;

END IF;

END;

INSERT INTO incremented_values

VALUES(1,1);

SELECT *

FROM incremented_values;

Any guesses? On my database I got this result:

SQL> SELECT *

2 FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED

-------------- -----------------

1 2

So, the increment_by_two trigger fired first and did nothing because the value_incremented column was not greater than 1; then the increment_by_one trigger fired to increase the value_incremented column by 1. Is this the result you will receive? The preceding example offers no guarantee. Will this always be the result on my database? Again, there is no guarantee. Prior to Oracle Database 11g, Oracle explicitly stated that there was no way to control or assure the order in which multiple triggers of the same type on a single table would fire. There are many theories, the most prevalent being that triggers fire in reverse order of creation or by order of object ID—but even those theories cannot be relied upon.

Starting with Oracle Database 11g, however, you can guarantee the firing order using the FOLLOWS clause, as shown in the following example:

TRIGGER increment_by_two

BEFORE INSERT ON incremented_values

FOR EACH ROW

FOLLOWS increment_by_one

BEGIN

IF :new.value_incremented > 1 THEN

:new.value_incremented := :new.value_incremented + 2;

END IF;

END;

Now this trigger is guaranteed to fire after the increment_by_one trigger does, thus guaranteeing the final result of the insert as well:

SQL> INSERT INTO incremented_values

2 VALUES(1,1);

1 row created.

SQL> SELECT *

2 FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED

-------------- -----------------

1 4

The increment_by_one trigger made the inserted value 2, and then the increment_by_two trigger bumped it up to 4. This will always be the behavior because it is specified within the trigger itself—no need to rely on theories.

The link between triggers and their followers is viewable as a reference dependency in the dependencies view of the Oracle data dictionary:

SQL> SELECT referenced_name,

2 referenced_type,

3 dependency_type

4 FROM user_dependencies

5 WHERE name = 'INCREMENT_BY_TWO'

6 AND referenced_type = 'TRIGGER';

REFERENCED_NAME REFERENCED_TYPE DEPE

------------------ ------------------ ----

INCREMENT_BY_ONE TRIGGER REF

Despite the behavior I’ve described here for Oracle Database 11g, triggers will not follow blindly—attempts to compile a trigger to follow one that is undefined are met with this error message:

Trigger "SCOTT"."BLIND_FOLLOWER" referenced in FOLLOWS or PRECEDES clause may

not exist

Mutating Table Errors

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-4091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Suppose, for example, that I want to put a special check on my employees table to make sure that when a person is given a raise, that person’s new salary is not more than 20% above the next-highest salary in his department.

I would therefore like to write a trigger like this:

TRIGGER brake_on_raises

BEFORE UPDATE OF salary ON employees

FOR EACH ROW

DECLARE

l_curr_max NUMBER;

BEGIN

SELECT MAX (salary) INTO l_curr_max

FROM employees

WHERE department_id = :NEW.department_id;

IF l_curr_max * 1.20 < :NEW.salary

THEN

errpkg.RAISE (

employee_rules.en_salary_increase_too_large,

:NEW.employee_id,

:NEW.salary

);

END IF;

END;

But when I try to perform an update that, say, doubles the salary of the PL/SQL programmer (yours truly), I get this error:

ORA-04091: table SCOTT.EMPLOYEE is mutating, trigger/function may not see it

Here are some guidelines to keep in mind regarding mutating table errors:

§ In general, a row-level trigger may not read or write the table from which it has been fired. The restriction applies only to row-level triggers, however. Statement-level triggers are free to both read and modify the triggering table; this fact gives us a way to avoid the mutating table error.

§ If you make your trigger an autonomous transaction (by adding the PRAGMA AUTONOMOUS TRANSACTION statement and committing inside the body of the trigger), then you will be able to query the contents of the firing table. However, you will still not be allowed to modify the contents of the table.

Because each release of the Oracle database renders mutating tables less and less of a problem, it’s not really necessary to perform a full demonstration here. However, a demonstration script named mutation_zone.sql is available on the book’s website. In addition, the filemutating_template.sql offers a package that can serve as a template for creating your own package to defer processing of row-level logic to the statement level.

Compound Triggers: Putting It All in One Place

The age-old saying, “I finally got it all together, but I forgot where I put it” often applies to triggers. As you create more and more triggers containing more and more business logic, it becomes difficult to recall which triggers handle which rules and how all of the triggers interact. In the previous section I demonstrated how the three types of DML (insert, update, delete) can be put together in a single trigger, but wouldn’t it be nice to be able to put row and statement triggers together in the same code object as well? Starting with Oracle Database 11g, you can use the compound trigger to do just that.

Here’s a very simple example to show the syntax:

/* File on web: compound_trigger.sql */

1 TRIGGER compounder

2 FOR UPDATE OR INSERT OR DELETE ON incremented_values

3 COMPOUND TRIGGER

4

5 v_global_var NUMBER := 1;

6

7 BEFORE STATEMENT IS

8 BEGIN

9 DBMS_OUTPUT.PUT_LINE('Compound:BEFORE S:' || v_global_var);

10 v_global_var := v_global_var + 1;

11 END BEFORE STATEMENT;

12

13 BEFORE EACH ROW IS

14 BEGIN

15 DBMS_OUTPUT.PUT_LINE('Compound:BEFORE R:' || v_global_var);

16 v_global_var := v_global_var + 1;

17 END BEFORE EACH ROW;

18

19 AFTER EACH ROW IS

20 BEGIN

21 DBMS_OUTPUT.PUT_LINE('Compound:AFTER R:' || v_global_var);

22 v_global_var := v_global_var + 1;

23 END AFTER EACH ROW;

24

25 AFTER STATEMENT IS

26 BEGIN

27 DBMS_OUTPUT.PUT_LINE('Compound:AFTER S:' || v_global_var);

28 v_global_var := v_global_var + 1;

29 END AFTER STATEMENT;

30

31 END;

Just like a package

Compound triggers look a lot like PL/SQL packages, don’t they? All of the related code and logic is in one place, making it easy to debug and modify. Let’s look at the syntax in detail.

The most obvious change is the COMPOUND TRIGGER statement, which advises Oracle that this trigger contains many triggers that it will need to make fire together.

The next (and most eagerly awaited) change appears somewhat innocently on line 5: a global variable! Finally, global variables can be defined together with the code that manages them—no more special packages to manage them like this:

PACKAGE BODY yet_another_global_package AS

v_global_var NUMBER := 1;

PROCEDURE reset_global_var IS

...

END;

The remaining compound trigger syntax is very similar to that of standalone triggers, but a bit more rigid:

BEFORE STATEMENT

The code in this section will fire before a DML statement executes, just like a standalone BEFORE statement trigger does.

BEFORE EACH ROW

The code in this section gets executed before each and every row is processed by the DML statement.

AFTER EACH ROW

The code in this section gets executed after each and every row is processed by the DML statement.

AFTER STATEMENT

The code in this section will fire after a DML statement executes, just like a standalone AFTER statement trigger does.

The rules for standalone triggers apply to compound triggers as well—for example, record values (OLD and NEW) cannot be modified in statement-level triggers.

Not just like a package

So compound triggers look like packages, but do they behave in the same way? The short answer is no—they behave better! Consider this example:

SQL> BEGIN

2 insert into incremented_values values(1,1);

3 insert into incremented_values values(2,2);

4 END;

5 /

Compound:BEFORE S:1

Compound:BEFORE R:2

Compound:AFTER R:3

Compound:AFTER S:4

Compound:BEFORE S:1

Compound:BEFORE R:2

Compound:AFTER R:3

Compound:AFTER S:4

PL/SQL procedure successfully completed.

Notice that the output of the global variable was set back to 1 when the second statement executed. That’s because the scope of the compound trigger is the DML statement that fires it. Once that statement completes, the compound trigger and its in-memory values start anew. That simplifies the logic.

A further benefit of the tight scoping is simplified error handling. I’ll demonstrate by putting a primary key on the table just so I can try to violate it later:

SQL> ALTER TABLE incremented_values

2 add constraint a_pk

3 primary key ( value_inserted );

Now to insert one record:

SQL> INSERT INTO incremented_values values(1,1);

Compound:BEFORE S:1

Compound:BEFORE R:2

Compound:AFTER R:3

Compound:AFTER S:4

1 row created.

No surprises so far. But the next INSERT should throw an error because it violates the new primary key:

SQL> INSERT INTO incremented_values values(1,1);

Compound:BEFORE S:1

Compound:BEFORE R:2

insert into incremented_values values(1,1)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.A_PK) violated

The next INSERT also throws the primary key error again, as expected. But that is not what’s exceptional about this situation—what’s exceptional is that the global variable was reinitialized back to 1 without any extra code having to be written. The firing DML completed, so the compound trigger fell out of scope and everything started anew for the next statement:

SQL> INSERT INTO incremented_values values(1,1);

Compound:BEFORE S:1

Compound:BEFORE R:2

insert into incremented_values values(1,1)

*

ERROR at line 1:

ORA-00001: unique constraint (DRH.A_PK) violated

I don’t need to include extra exception handling or packages just to reset the values when exceptions occur.

Compound following

Compound triggers also can be used with the FOLLOWS syntax:

TRIGGER follows_compounder

BEFORE INSERT ON incremented_values

FOR EACH ROW

FOLLOWS compounder

BEGIN

DBMS_OUTPUT.PUT_LINE('Following Trigger');

END;

Here’s the output:

SQL> INSERT INTO incremented_values

2 values(8,8);

Compound:BEFORE S:1

Compound:BEFORE R:2

Following Trigger

Compound:AFTER R:3

Compound:AFTER S:4

1 row created.

The specific triggers within the compound trigger cannot be defined to fire after any standalone or compound triggers.

NOTE

If a standalone trigger is defined to follow a compound trigger that does not contain a trigger to fire on the same statement or row, then the FOLLOWS clause is simply ignored.

DDL Triggers

Oracle allows you to define triggers that will fire when DDL statements are executed. Simply put, a DDL statement is any SQL statement used to create or modify a database object such as a table or an index. Here are some examples of DDL statements:

§ CREATE TABLE

§ ALTER INDEX

§ DROP TRIGGER

Each of these statements results in the creation, alteration, or removal of a database object.

The syntax for creating these triggers is remarkably similar to that of DML triggers, except that the firing events differ, and they are not applied to individual tables.

NOTE

The INSTEAD OF CREATE TABLE trigger, described at the end of this section, allows the default behavior of a CREATE TABLE event to be manipulated and is a somewhat idiosyncratic DDL trigger. Not all of the aspects of syntax and usage described in the following subsections apply to this trigger type.

Creating a DDL Trigger

To create (or replace) a DDL trigger, use the syntax shown here:

1 CREATE [OR REPLACE] TRIGGER trigger name

2 {BEFORE | AFTER} {DDL event} ON {DATABASE | SCHEMA}

3 [WHEN (...)]

4 DECLARE

5 Variable declarations

6 BEGIN

7 ...some code...

8 END;

The following table summarizes what is happening in this code.

Here’s an example of a somewhat uninformed town crier trigger that announces the creation of all objects:

/* File on web: uninformed_town_crier.sql */

SQL> CREATE OR REPLACE TRIGGER town_crier

2 AFTER CREATE ON SCHEMA

3 BEGIN

4 DBMS_OUTPUT.PUT_LINE('I believe you have created something!');

5 END;

6 /

Trigger created.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TABLE a_table

2 (col1 NUMBER);

Table created.

SQL> CREATE INDEX an_index ON a_table(col1);

Index created.

SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS

2 BEGIN

3 RETURN(TRUE);

4 END;

5 /

Function created.

SQL> /* flush the DBMS_OUTPUT buffer */

SQL> BEGIN NULL; END;

2 /

I believe you have created something!

I believe you have created something!

I believe you have created something!

PL/SQL procedure successfully completed.

WARNING

Text displayed using the DBMS_OUTPUT built-in package within DDL triggers will not display until you successfully execute a PL/SQL block, even if that block does nothing.

Over time, this town crier would be ignored due to a lack of information, always proudly announcing that something had been created but never providing any details. Thankfully, there is a lot more information available to DDL triggers, allowing for a much more nuanced treatment, as shown in this version:

/* File on web: informed_town_crier.sql */

SQL> CREATE OR REPLACE TRIGGER town_crier

2 AFTER CREATE ON SCHEMA

3 BEGIN

4 -- use event attributes to provide more info

5 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' ||

6 ORA_DICT_OBJ_TYPE || ' called ' ||

7 ORA_DICT_OBJ_NAME);

8 END;

9 /

Trigger created.

SQL> SET SERVEROUTPUT ON

SQL> CREATE TABLE a_table

2 col1 NUMBER);

Table created.

SQL> CREATE INDEX an_index ON a_table(col1);

Index created.

SQL> CREATE FUNCTION a_function RETURN BOOLEAN AS

2 BEGIN

3 RETURN(TRUE);

4 END;

5 /

Function created.

SQL> /* flush the DBMS_OUTPUT buffer */

SQL> BEGIN NULL; END;

2 /

I believe you have created a TABLE called A_TABLE

I believe you have created a INDEX called AN_INDEX

I believe you have created a FUNCTION called A_FUNCTION

PL/SQL procedure successfully completed.

Much more attention will be paid now that the town crier is more forthcoming. The preceding examples touch upon two important aspects of DDL triggers: the specific events to which they can be applied and the event attributes available within the triggers.

Available Events

Table 19-1 lists the DDL events for which triggers can be coded. Each event can have a BEFORE and an AFTER trigger.

Table 19-1. Available DDL events

As with DML triggers, these DDL triggers fire when the events to which they are attached occur within the specified database or schema. There is no limit to the number of trigger types that can exist in the database or schema.

Available Attributes

Oracle provides a set of functions (defined in the DBMS_STANDARD package) that provide information about what fired the DDL trigger and other information about the trigger state (e.g., the name of the table being dropped). Table 19-2 displays these trigger attribute functions. The following sections offer some examples of usage.

Table 19-2. DDL trigger event and attribute functions

Note the following about the event and attribute functions:

§ The datatype ORA_NAME_LIST_T is defined in the DBMS_STANDARD package as:

TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);

In other words, this is a nested table of strings, each of which can contain up to 64 characters.

§ The DDL trigger event and attribute functions are also defined in the DBMS_STANDARD package. Oracle creates a standalone function (which adds the “ORA_” prefix to the function name) for each of the packaged functions by executing the $ORACLE_HOME/rdbms/dbmstrig.sqlscript during database creation. In some releases of the Oracle database, there are errors in this script that cause the standalone functions to not be visible or executable. If you feel that these elements have not been properly defined, you should ask your DBA to check the script for problems and make the necessary corrections.

§ The USER_SOURCE data dictionary view does not get updated until after both BEFORE and AFTER DDL triggers are fired. In other words, you cannot use these functions to provide a “before and after” version control system built entirely within the database and based on database triggers.

Working with Events and Attributes

The best way to demonstrate the possibilities offered by DDL trigger events and attributes is with a series of examples.

Here is a trigger that prevents any and all database objects from being created:

TRIGGER no_create

AFTER CREATE ON SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (

−20000,

'ERROR : Objects cannot be created in the production database.'

);

END;

After I install this trigger, attempts at creating anything meet with failure:

SQL> CREATE TABLE demo (col1 NUMBER);

*

ERROR at line 1:

ORA-20000: Objects cannot be created in the production database.

That is a rather terse and uninformative error message. There was a failure, but what failed? Wouldn’t it be nice to have a little more information in the error message, such as the object I was attempting to create? Consider this version:

/* File on web: no_create.sql */

TRIGGER no_create

AFTER CREATE ON SCHEMA

BEGIN

RAISE_APPLICATION_ERROR (-20000,

'Cannot create the ' || ORA_DICT_OBJ_TYPE ||

' named ' || ORA_DICT_OBJ_NAME ||

' as requested by ' || ORA_DICT_OBJ_OWNER ||

' in production.');

END;

With this trigger installed, an attempt to create my table now offers much more diagnostic information:

SQL> CREATE TABLE demo (col1 NUMBER);

*

ERROR at line 1:

ORA-20000: Cannot create the TABLE named DEMO as requested by SCOTT in production

I could even place this logic within a BEFORE DDL trigger and take advantage of the ORA_SYSEVENT attribute to respond to specific events:

TRIGGER no_create

BEFORE DDL ON SCHEMA

BEGIN

IF ORA_SYSEVENT = 'CREATE'

THEN

RAISE_APPLICATION_ERROR (-20000,

'Cannot create the ' || ORA_DICT_OBJ_TYPE ||

' named ' || ORA_DICT_OBJ_NAME ||

' as requested by ' || ORA_DICT_OBJ_OWNER);

ELSIF ORA_SYSEVENT = 'DROP'

THEN

-- Logic for DROP operations

...

END IF;

END;

What column did I touch?

I can use the ORA_IS_ALTER_COLUMN function to decipher which column was altered by an ALTER TABLE statement. Here is one example:

/* File on web: preserve_app_cols.sql */

TRIGGER preserve_app_cols

AFTER ALTER ON SCHEMA

DECLARE

-- Cursor to get columns in a table

CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2)

IS

SELECT column_name

FROM all_tab_columns

WHERE owner = cp_owner AND table_name = cp_table;

BEGIN

-- If it was a table that was altered...

IF ora_dict_obj_type = 'TABLE'

THEN

-- for every column in the table...

FOR v_column_rec IN curs_get_columns (

ora_dict_obj_owner,

ora_dict_obj_name

)

LOOP

-- Is the current column one that was altered?

IF ORA_IS_ALTER_COLUMN (v_column_rec.column_name)

THEN

-- Reject change to "core application" column

IF mycheck.is_application_column (

ora_dict_obj_owner,

ora_dict_obj_name,

v_column_rec.column_name

)

THEN

CENTRAL_ERROR_HANDLER (

'FAIL',

'Cannot alter core application attributes'

);

END IF; -- table/column is core

END IF; -- current column was altered

END LOOP; -- every column in the table

END IF; -- table was altered

END;

Attempts to change core application attributes will now be stopped.

Remember that this logic will not work when the trigger is fired for the addition of new columns. That column information is not yet visible in the data dictionary when the DDL trigger fires.

I can check for attempts to drop specific columns as follows:

IF ORA_IS_DROP_COLUMN ('COL2')

THEN

do something!

ELSE

do something else!

END IF;

NOTE

The ORA_IS_DROP_COLUMN and ORA_IS_ALTER_COLUMN functions are blissfully unaware of the table to which the column is attached; they work on column name alone.

Lists returned by attribute functions

Some of the attribute functions return two pieces of data: a list of items and a count of items. For example, the ORA_GRANTEE function returns a list and a count of users that were granted a privilege, and the ORA_PRIVILEGE_LIST function returns a list and a count of privileges granted. These two functions are perfect for use in AFTER GRANT triggers. The what_privs.sql file available on the book’s website offers an extended example of how to use both of these functions. Here is just a portion of the total code:

/* File on web: what_privs.sql */

TRIGGER what_privs

AFTER GRANT ON SCHEMA

DECLARE

v_grant_type VARCHAR2 (30);

v_num_grantees BINARY_INTEGER;

v_grantee_list ora_name_list_t;

v_num_privs BINARY_INTEGER;

v_priv_list ora_name_list_t;

BEGIN

-- Retrieve information about grant type and then the lists.

v_grant_type := ORA_DICT_OBJ_TYPE;

v_num_grantees := ORA_GRANTEE (v_grantee_list);

v_num_privs := ORA_PRIVILEGE_LIST (v_priv_list);

IF v_grant_type = 'ROLE PRIVILEGE'

THEN

DBMS_OUTPUT.put_line (

'The following roles/privileges were granted');

-- For each element in the list, display the privilege.

FOR counter IN 1 .. v_num_privs

LOOP

DBMS_OUTPUT.put_line ('Privilege ' || v_priv_list (counter));

END LOOP;

This trigger is great for detailing what privileges and objects are affected by grant operations, as shown next. In a more sophisticated implementation, you might consider storing this information in database tables so that you have a detailed history of changes that have occurred:

SQL> GRANT DBA TO book WITH ADMIN OPTION;

Grant succeeded.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer');

The following roles/privileges were granted

Privilege UNLIMITED TABLESPACE

Privilege DBA

Grant Recipient BOOK

Flush buffer

SQL> GRANT SELECT ON x TO system WITH GRANT OPTION;

Grant succeeded.

SQL> EXEC DBMS_OUTPUT.PUT_LINE('Flush buffer');

The following object privileges were granted

Privilege SELECT

On X with grant option

Grant Recipient SYSTEM

Flush buffer

Dropping the Undroppable

I have shown that one use for DDL triggers is preventing a particular type of DDL operation on a particular object or type of object. But what if I create a trigger that prevents DDL DROP operations and then attempt to drop the trigger itself? Will I be left with a trigger that is essentially undroppable? Fortunately, Oracle has thought of this scenario, as you can see here:

SQL> CREATE OR REPLACE TRIGGER undroppable

2 BEFORE DROP ON SCHEMA

3 BEGIN

4 RAISE_APPLICATION_ERROR(-20000,'You cannot drop me! I am invincible!');

5 END;

SQL> DROP TABLE employee;

*

ERROR at line 1:

ORA-20000: You cannot drop me! I am invincible!

SQL> DROP TRIGGER undroppable;

Trigger dropped.

NOTE

When working with pluggable databases (Oracle Database 12c and higher), you can insert the word PLUGGABLE before DATABASE in your trigger definition. DATABASE (without PLUGGABLE) defines the trigger on the root. In a multitenant container database (CDB), only a common user who is connected to the root can create a trigger on the entire database. PLUGGABLE DATABASE defines the trigger on the PDB to which you are connected. The trigger fires whenever any user of the specified database or PDB initiates the triggering event.

The INSTEAD OF CREATE Trigger

Oracle provides the INSTEAD OF CREATE trigger to allow you to automatically partition a table. To do so, the trigger must trap the SQL statement being executed, insert the partition clause into it, and then execute it using the ORA_SQL_TXT function. The following trigger demonstrates these steps:

/* File on web: io_create.sql */

TRIGGER io_create

INSTEAD OF CREATE ON DATABASE

WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')

DECLARE

v_sql VARCHAR2 (32767); -- sql to be built

v_sql_t ora_name_list_t; -- table of sql

BEGIN

-- get the SQL statement being executed

FOR counter IN 1 .. ora_sql_txt (v_sql_t)

LOOP

v_sql := v_sql || v_sql_t (counter);

END LOOP;

-- Determine the partition clause and add it.

-- We will call the my_partition function.

v_sql :=

SUBSTR (v_sql, 1, ora_partition_pos)

|| magic_partition_function

|| SUBSTR (v_sql, ora_partition_pos + 1);

/* Prepend table name with login username.

| Replace CRLFs with spaces.

| Requires an explicit CREATE ANY TABLE privilege,

| unless you switch to AUTHID CURRENT_USER.

*/

v_sql :=

REPLACE (UPPER (REPLACE (v_sql, CHR (10), ' '))

, 'CREATE TABLE '

, 'CREATE TABLE ' || ora_login_user || '.'

);

-- now execute the SQL

EXECUTE IMMEDIATE v_sql;

END;

Now tables will be partitioned automatically, as determined by the logic in the my_partition function.

Oracle offers several partitioning options (e.g., range, hash) and logical partitioning choices (e.g., by primary key, by unique key). You must decide which of these you want to utilize in your partitioning function.

If you do not include the WHEN clause just shown, you will find that attempts to create objects that are not tables will fail with this error:

ORA-00604: error occurred at recursive SQL level 1

ORA-30511: invalid DDL operation in system triggers

Further, if you try to create an INSTEAD OF trigger for any other DDL operation besides CREATE, you will receive this compilation error:

ORA-30513: cannot create system triggers of INSTEAD OF type

NOTE

INSTEAD OF triggers for DML operations (insert, update, and delete) are addressed later in this chapter. These triggers share some syntax with the INSTEAD OF CREATE trigger for tables, but that is the extent of their similarity.

Database Event Triggers

Database event triggers fire whenever database-wide events occur. There are eight database event triggers, two of which are new to Oracle Database 12c:

STARTUP

Fires when the database is opened.

SHUTDOWN

Fires when the database is shut down normally.

SERVERERROR

Fires when an Oracle error is raised.

LOGON

Fires when an Oracle database session begins.

LOGOFF

Fires when an Oracle database session terminates normally.

DB_ROLE_CHANGE

Fires when a standby database is changed to be the primary database, or vice versa.

AFTER_CLONE (Oracle Database 12c)

Can be specified only if PLUGGABLE DATABASE is specified. After the pluggable database (PDB) is copied (cloned), the database fires the trigger in the new PDB and then deletes the trigger. If the trigger fails, then the copy operation fails.

BEFORE UNPLUG (Oracle Database 12c)

Can be specified only if PLUGGABLE DATABASE is specified. Before the PDB is unplugged, the database fires the trigger and then deletes it. If the trigger fails, then the unplug operation fails.

As any DBA will immediately see, these triggers offer stunning possibilities for automated administration and very granular control.

Creating a Database Event Trigger

The syntax used to create these triggers is quite similar to that used for DDL triggers:

1 CREATE [OR REPLACE] TRIGGER trigger_name

2 {BEFORE | AFTER} {database_event} ON {DATABASE | SCHEMA}

3 DECLARE

4 Variable declarations

5 BEGIN

6 ...some code...

7 END;

There are restrictions regarding what events can be combined with what BEFORE and AFTER attributes. Some situations just don’t make sense:

No BEFORE STARTUP triggers

Even if such triggers could be created, when would they fire? Attempts to create triggers of this type will be met by this straightforward error message:

ORA-30500: database open triggers and server error triggers cannot have

BEFORE type

No AFTER SHUTDOWN triggers

Again, when would they fire? Attempts to create such triggers are deflected with this message:

ORA-30501: instance shutdown triggers cannot have AFTER type

No BEFORE LOGON triggers

It would require some amazingly perceptive code to implement these triggers: “Wait, I think someone is going to log on—do something!” Being strictly reality-based, Oracles stops these triggers with this message:

ORA-30508: client logon triggers cannot have BEFORE type

No AFTER LOGOFF triggers

“No wait, please come back! Don’t sign off!” Attempts to create such triggers are stopped with this message:

ORA-30509: client logoff triggers cannot have AFTER type

No BEFORE SERVERERROR

These triggers would be every programmer’s dream! Think of the possibilities...

CREATE OR REPLACE TRIGGER BEFORE_SERVERERROR

BEFORE SERVERERROR ON DATABASE

BEGIN

diagnose_impending_error;

fix_error_condition;

continue_as_if_nothing_happened;

END;

Unfortunately, our dreams are shattered by this error message:

ORA-30500: database open triggers and server error triggers cannot have

BEFORE type

The STARTUP Trigger

Startup triggers execute during database startup processing. This is a perfect place to perform housekeeping steps, such as pinning objects in the shared pool so that they do not “age out” with the least recently used algorithm.

NOTE

In order to create startup event triggers, users must have been granted the ADMINISTER DATABASE TRIGGER privilege.

Here is an example of creating a STARTUP event trigger:

CREATE OR REPLACE TRIGGER startup_pinner

AFTER STARTUP ON DATABASE

BEGIN

pin_plsql_packages;

pin_application_packages;

END;

The SHUTDOWN Trigger

BEFORE SHUTDOWN triggers execute before database shutdown processing is performed. This is a great place to gather system statistics. Here is an example of creating a SHUTDOWN event trigger:

CREATE OR REPLACE TRIGGER before_shutdown

BEFORE SHUTDOWN ON DATABASE

BEGIN

gather_system_stats;

END;

NOTE

SHUTDOWN triggers execute only when the database is shut down using NORMAL or IMMEDIATE mode. They do not execute when the database is shut down using ABORT mode or when the database crashes.

The LOGON Trigger

AFTER LOGON triggers fire when an Oracle database session is begun. They are the perfect place to establish session context and perform other session setup tasks. Here is an example of creating a LOGON event trigger:

TRIGGER after_logon

AFTER LOGON ON SCHEMA

DECLARE

v_sql VARCHAR2(100) := 'ALTER SESSION ENABLE RESUMABLE ' ||

'TIMEOUT 10 NAME ' || '''' ||

'OLAP Session' || '''';

BEGIN

EXECUTE IMMEDIATE v_sql;

DBMS_SESSION.SET_CONTEXT('OLAP Namespace',

'Customer ID',

load_user_customer_id);

END;

The LOGOFF Trigger

BEFORE LOGOFF triggers execute when sessions disconnect normally from the database. This is a good place to gather statistics regarding session activity. Here is an example of creating a LOGOFF event trigger:

TRIGGER before_logoff

BEFORE LOGOFF ON DATABASE

BEGIN

gather_session_stats;

END;

The SERVERERROR Trigger

AFTER SERVERERROR triggers fire after an Oracle error is raised, unless the error is one of the following:

ORA-00600

Oracle internal error

ORA-01034

Oracle not available

ORA-01403

No data found

ORA-01422

Exact fetch returns more than requested number of rows

ORA-01423

Error encountered while checking for extra rows in an exact fetch

ORA-04030

Out-of-process memory when trying to allocate N bytes

In addition, the AFTER SERVERERROR trigger will not fire when an exception is raised inside this trigger (to avoid an infinite recursive execution of the trigger).

AFTER SERVERERROR triggers do not provide facilities to fix the error, only to log information about the error. It is therefore possible to build some powerful logging mechanisms around these triggers.

Oracle also provides built-in functions (again, defined in DBMS_STANDARD) that retrieve information about the error stack generated when an exception is raised:

ORA_SERVER_ERROR

Returns the Oracle error number at the specified position in the error stack. It returns 0 if no error is found at that position.

ORA_IS_SERVERERROR

Returns TRUE if the specified error number appears in the current exception stack.

ORA_SERVER_ERROR_DEPTH

Returns the number of errors on the stack.

ORA_SERVER_ERROR_MSG

Returns the full text of the error message at the specified position. It returns NULL if no error is found at that position.

ORA_SERVER_ERROR_NUM_PARAMS

Returns the number of parameters associated with the error message at the given position. It returns 0 if no error is found at that position.

ORA_SERVER_ERROR_PARAM

Returns the value for the specified parameter position in the specified error. It returns NULL if none is found.

SERVERERROR examples

Let’s look at some examples of using SERVERERROR triggers. I’ll start with a very simple example of a SERVERERROR trigger that echoes the fact that an error occurred:

TRIGGER error_echo

AFTER SERVERERROR

ON SCHEMA

BEGIN

DBMS_OUTPUT.PUT_LINE ('You experienced an error');

END;

Whenever an Oracle error occurs (assuming that SERVEROUTPUT is ON), the preceding coded message will display:

SQL> SET SERVEROUTPUT ON

SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));

You experienced an error

BEGIN DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A')); END;

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 1

Note that the Oracle error message was delivered after the trigger message. This allows the error to be accessed and logged prior to the actual failure, as shown in the next example.

NOTE

SERVERERROR triggers are automatically isolated in their own autonomous transaction (autonomous transactions were covered in Chapter 14). This means that you can, for example, write error information out to a log table and save those changes with a COMMIT, while not affecting the session transaction in which the error occurred.

The error_logger trigger guarantees that information about all but a handful of errors (listed earlier) will be automatically logged, regardless of the application, user, or program in which the error was raised:

/* File on web: error_log.sql */

TRIGGER error_logger

AFTER SERVERERROR

ON SCHEMA

DECLARE

v_errnum NUMBER; -- the Oracle error #

v_now DATE := SYSDATE; -- current time

BEGIN

-- For every error in the error stack...

FOR e_counter IN 1..ORA_SERVER_ERROR_DEPTH LOOP

-- Write the error out to the log table; no

-- commit is required because we are in an

-- autonomous transaction.

INSERT INTO error_log(error_id,

username,

error_number,

sequence,

timestamp)

VALUES(error_seq.nextval,

USER,

ORA_SERVER_ERROR(e_counter),

e_counter,

v_now);

END LOOP; -- every error on the stack

END;

Remember that all these new rows in the error_log have been committed by the time the END statement is reached, because the trigger is executed within an autonomous transaction. The following lines demonstrate this trigger in action:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

SQL> SELECT * FROM error_log;

USERNAME ERROR_NUMBER SEQUENCE TIMESTAMP

------------------------------ ------------ ---------- ---------

BOOK 6502 1 04-JAN-02

BOOK 6512 2 04-JAN-02

Why do two errors appear in the table when only one error was raised? The actual error stack generated by the database contains both ORA-06502 and ORA-06512, so they are both logged and denoted by their sequence of occurrence.

If you want to determine quickly if a certain error number is located in the stack without parsing it manually, use the companion function ORA_IS_SERVERERROR. This function is very useful for monitoring specific errors that may require extra handling, such as user-defined exceptions. This is the kind of code you might write:

-- Special handling of user-defined errors

-- 20000 through 20010 raised by calls to

-- RAISE_APPLICATION_ERROR

FOR errnum IN 20000 .. 20010

LOOP

IF ORA_IS_SERVERERROR (errnum)

THEN

log_user_defined_error (errnum);

END IF;

END LOOP;

NOTE

All Oracle error numbers are negative, except for 1 (user-defined exception) and 100 (synonymous with 1403, NO_DATA_FOUND). When you specify an error number in the call to ORA_IS_SERVERERROR, however, you must supply a positive number, as shown in the previous example.

Central error handler

While it is possible to implement separate SERVERERROR triggers in every schema in a database, I recommend creating a single central trigger with an accompanying PL/SQL package to provide the following features:

Centralized error logging

There is only one trigger and package to maintain and keep in Oracle’s memory.

Session-long searchable error log

The error log can be accumulated over the course of a session rather than error by error. It can be searched to return details like the number of occurrences, the timestamps of the first and last occurrence, etc. The log can also be purged on demand.

Option to save error log

The error log can be saved to a permanent table in the database if desired.

Viewable current log

The current log of errors is viewable by specific error number and/or date range.

You can find the implementation of one such centralized error-handling package in the error_log.sql file on the book’s website. Once this package is in place, I can create the SERVERERROR trigger as follows:

CREATE OR REPLACE TRIGGER error_log

AFTER SERVERERROR

ON DATABASE

BEGIN

central_error_log.log_error;

END;

Here are some example usages. First, I will generate an error:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(TO_NUMBER('A'));

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Now I can search for a specific error number and retrieve that information in a record:

DECLARE

v_find_record central_error_log.v_find_record;

BEGIN

central_error_log.find_error(6502,v_find_record);

DBMS_OUTPUT.PUT_LINE('Total Found = ' || v_find_record.total_found);

DBMS_OUTPUT.PUT_LINE('Min Timestamp = ' || v_find_record.min_timestamp);

DBMS_OUTPUT.PUT_LINE('Max Timestamp = ' || v_find_record.max_timestamp);

END;

The output is:

Total Found = 1

Min Timestamp = 04-JAN-02

Max Timestamp = 04-JAN-02

INSTEAD OF Triggers

INSTEAD OF triggers control insert, update, merge, and delete operations on views, not tables. They can be used to make nonupdateable views updateable and to override the default behavior of views that are updateable.

Creating an INSTEAD OF Trigger

To create (or replace) an INSTEAD OF trigger, use the syntax shown here:

1 CREATE [OR REPLACE] TRIGGER trigger_name

2 INSTEAD OF operation

3 ON view_name

4 FOR EACH ROW

5 BEGIN

6 ...code goes here...

7 END;

The following table contains an explanation of this code.

INSTEAD OF triggers are best explained with an example. Let’s use one of my favorite topics: pizza delivery! Before I can start pounding the dough, I have to put a system in place to monitor my deliveries. I will need three tables—one to track actual deliveries, one to track delivery areas, and one to track my massive fleet of drivers (remember the first rule of business—always think big!):

/* File on web: pizza_tables.sql */

CREATE TABLE delivery

(delivery_id NUMBER,

delivery_start DATE,

delivery_end DATE,

area_id NUMBER,

driver_id NUMBER);

CREATE TABLE area

(area_id NUMBER, area_desc VARCHAR2(30));

CREATE TABLE driver

(driver_id NUMBER, driver_name VARCHAR2(30));

For the sake of brevity, I will not create any primary or foreign keys.

I will also need three sequences to provide unique identifiers for my tables:

CREATE SEQUENCE delivery_id_seq;

CREATE SEQUENCE area_id_seq;

CREATE SEQUENCE driver_id_seq;

To avoid having to explain relational database design and normalization to my employees, I will simplify deliveries into a single view displaying delivery, area, and driver information:

VIEW delivery_info AS

SELECT d.delivery_id,

d.delivery_start,

d.delivery_end,

a.area_desc,

dr.driver_name

FROM delivery d,

area a,

driver dr

WHERE a.area_id = d.area_id

AND dr.driver_id = d.driver_id

Because my system relies heavily on this view for query functionality, why not make it available for insert, update, and delete operations as well? I cannot directly issue DML statements against the view; it is a join of multiple tables. How would the database know what to do with an INSERT? In fact, I need to tell the database very explicitly what to do when an insert, update, or delete is issued against the delivery_info view; in other words, I need to tell it what to do instead of trying to insert, update, or delete. Thus, I will use INSTEAD OF triggers. Let’s start with the INSERT trigger.

The INSTEAD OF INSERT Trigger

My INSERT trigger will perform four basic operations:

§ Ensure that the delivery_end value is NULL. All delivery completions must be done via an update.

§ Try to find the driver ID based on the name provided. If the name cannot be found, then assign a new ID and create a driver entry using the name and the new ID.

§ Try to find the area ID based on the name provided. If the name cannot be found, then assign a new ID and create an area entry using the name and the new ID.

§ Create an entry in the delivery table.

Bear in mind that this example is intended to demonstrate triggers—not how to effectively build a business system! After a while I will probably wind up with a multitude of duplicate driver and area entries. However, using this view speeds things up by not requiring drivers and areas to be predefined, and in the fast-paced world of pizza delivery, time is money!

/* File on web: pizza_triggers.sql */

TRIGGER delivery_info_insert

INSTEAD OF INSERT

ON delivery_info

DECLARE

-- cursor to get the driver ID by name

CURSOR curs_get_driver_id (cp_driver_name VARCHAR2)

IS

SELECT driver_id

FROM driver

WHERE driver_name = cp_driver_name;

v_driver_id NUMBER;

-- cursor to get the area ID by name

CURSOR curs_get_area_id (cp_area_desc VARCHAR2)

IS

SELECT area_id

FROM area

WHERE area_desc = cp_area_desc;

v_area_id NUMBER;

BEGIN

/*

|| Make sure the delivery_end value is NULL.

*/

IF :NEW.delivery_end IS NOT NULL

THEN

raise_application_error

(-20000

, 'Delivery end date value must be NULL when delivery created'

);

END IF;

/*

|| Try to get the driver ID using the name. If not found

|| then create a brand new driver ID from the sequence.

*/

OPEN curs_get_driver_id (UPPER (:NEW.driver_name));

FETCH curs_get_driver_id

INTO v_driver_id;

IF curs_get_driver_id%NOTFOUND

THEN

SELECT driver_id_seq.NEXTVAL

INTO v_driver_id

FROM DUAL;

INSERT INTO driver

(driver_id, driver_name

)

VALUES (v_driver_id, UPPER (:NEW.driver_name)

);

END IF;

CLOSE curs_get_driver_id;

/*

|| Try to get the area ID using the name. If not found

|| then create a brand new area ID from the sequence.

*/

OPEN curs_get_area_id (UPPER (:NEW.area_desc));

FETCH curs_get_area_id

INTO v_area_id;

IF curs_get_area_id%NOTFOUND

THEN

SELECT area_id_seq.NEXTVAL

INTO v_area_id

FROM DUAL;

INSERT INTO area

(area_id, area_desc

)

VALUES (v_area_id, UPPER (:NEW.area_desc)

);

END IF;

CLOSE curs_get_area_id;

/*

|| Create the delivery entry.

*/

INSERT INTO delivery

(delivery_id, delivery_start

, delivery_end, area_id, driver_id

)

VALUES (delivery_id_seq.NEXTVAL, NVL (:NEW.delivery_start, SYSDATE)

, NULL, v_area_id, v_driver_id

);

END;

The INSTEAD OF UPDATE Trigger

Now let’s move on to the UPDATE trigger. For the sake of simplicity, I will allow updating only of the delivery_end field, and only if it is NULL to start with. I can’t have drivers resetting delivery times!

/* File on web: pizza_triggers.sql */

TRIGGER delivery_info_update

INSTEAD OF UPDATE

ON delivery_info

DECLARE

-- cursor to get the delivery entry

CURSOR curs_get_delivery (cp_delivery_id NUMBER)

IS

SELECT delivery_end

FROM delivery

WHERE delivery_id = cp_delivery_id

FOR UPDATE OF delivery_end;

v_delivery_end DATE;

BEGIN

OPEN curs_get_delivery (:NEW.delivery_id);

FETCH curs_get_delivery INTO v_delivery_end;

IF v_delivery_end IS NOT NULL

THEN

RAISE_APPLICATION_ERROR (

−20000, 'The delivery end date has already been set');

ELSE

UPDATE delivery

SET delivery_end = :NEW.delivery_end

WHERE CURRENT OF curs_get_delivery;

END IF;

CLOSE curs_get_delivery;

END;

The INSTEAD OF DELETE Trigger

The DELETE trigger is the simplest of all. It merely ensures that I am not deleting a completed entry and then removes the delivery record. The driver and area records remain intact:

/* File on web: pizza_triggers.sql */

TRIGGER delivery_info_delete

INSTEAD OF DELETE

ON delivery_info

BEGIN

IF :OLD.delivery_end IS NOT NULL

THEN

RAISE_APPLICATION_ERROR (

−20000,'Completed deliveries cannot be deleted');

END IF;

DELETE delivery

WHERE delivery_id = :OLD.delivery_id;

END;

Populating the Tables

Now, with a single INSERT focused on the delivery information I know (the driver and the area), all of the required tables are populated:

SQL> INSERT INTO delivery_info(delivery_id,

2 delivery_start,

3 delivery_end,

4 area_desc,

5 driver_name)

6 VALUES

7 NULL, NULL, NULL, 'LOCAL COLLEGE', 'BIG TED');

1 row created.

SQL> SELECT * FROM delivery;

DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID

----------- --------- --------- ---------- ----------

1 13-JAN-02 1 1

SQL> SELECT * FROM area;

AREA_ID AREA_DESC

---------- ------------------------------

1 LOCAL COLLEGE

SQL> SELECT * FROM driver;

DRIVER_ID DRIVER_NAME

---------- ------------------------------

1 BIG TED

INSTEAD OF Triggers on Nested Tables

Oracle has introduced many ways to store complex data structures as columns in tables or views. This is logically effective because the linkage between a table or view and its columns is obvious. Technically, it can require some not-so-obvious trickery to allow even the simplest of operations, like inserting records into these complex structures. One of these complex situations can be resolved with a special type of INSTEAD OF trigger, as shown in this section.

Consider the following view joining each of the chapters of a book with the lines in that chapter:

VIEW book_chapter_view AS

SELECT chapter_number,

chapter_title,

CAST(MULTISET(SELECT *

FROM book_line

WHERE chapter_number = book_chapter.chapter_number)

AS book_line_t) lines

FROM book_chapter;

I agree that the view is far too obtuse for its purpose (why not just join the tables directly?), but it easily demonstrates the use of INSTEAD OF triggers on nested table columns—or on any object or collection column in a view.

After creating a record in the book_chapter table and querying the view, I’ll see the following, which explains that there are no lines in the chapter yet:

CHAPTER_NUMBER CHAPTER_TITLE

-------------- ------------------------------

LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)

---------------------------------------------

18 Triggers

BOOK_LINE_T()

So I then try to create the first line to get past my writer’s block:

SQL> INSERT INTO TABLE(SELECT lines

2 FROM book_chapter_view

3 WHERE chapter_number = 18)

4 VALUES(18,1,'Triggers are...');

INSERT INTO TABLE(SELECT lines

*

ERROR at line 1:

ORA-25015: cannot perform DML on this nested table view column

Apparently, the database has determined that there is not enough information available to just insert values into the book_line table masquerading as the LINES column in the view. Thus, an INSTEAD OF trigger is required to make the intent crystal clear:

TRIGGER lines_ins

INSTEAD OF INSERT ON NESTED TABLE lines OF book_chapter_view

BEGIN

INSERT INTO book_line

(chapter_number,

line_number,

line_text)

VALUES(:PARENT.chapter_number,

:NEW.line_number,

:NEW.line_text);

END;

Now I can add the first line:

SQL> INSERT INTO TABLE ( SELECT lines

2 FROM book_chapter_view

3 WHERE chapter_number = 18 )

4 VALUES(18,1,'Triggers Are...');

1 row created.

SQL> SELECT *

2 FROM book_chapter_view;

CHAPTER_NUMBER CHAPTER_TITLE

-------------- ------------------------------

LINES(CHAPTER_NUMBER, LINE_NUMBER, LINE_TEXT)

-----------------------------------------------------

18 Triggers

BOOK_LINE_T(BOOK_LINE_O(18, 1, 'Triggers Are...'))

Note that the SQL used to create the trigger is just like what is used for other INSTEAD OF triggers, except for two things:

§ The ON NESTED TABLE COLUMN OF clause used to denote the involved column

§ The new PARENT pseudo-record containing values from the view’s parent record

AFTER SUSPEND Triggers

Oracle9i Database Release 1 introduced a new type of trigger that fires whenever a statement is suspended. This might occur as the result of a space issue, such as exceeding an allocated tablespace quota. This functionality can be used to address the problem and allow the stalled operation to continue. AFTER SUSPEND triggers are a boon to busy developers tired of being held up by space errors, and to even busier DBAs who constantly have to resolve these errors.

The syntax used to create an AFTER SUSPEND trigger follows the same format as DDL and database event triggers. It declares the firing event (SUSPEND), the timing (AFTER), and the scope (DATABASE or SCHEMA):

1 CREATE [OR REPLACE] TRIGGER trigger_name

2 AFTER SUSPEND

3 ON {DATABASE | SCHEMA}

4 BEGIN

5 ...code...

6 END;

Let’s take a closer look at AFTER SUSPEND, starting with an example of a scenario that would call for creation of this type of trigger.

Consider the situation faced by Batch Only, the star Oracle developer at Totally Controlled Systems. He is responsible for maintaining hundreds of programs that run overnight, performing lengthy transactions to summarize information and move it between disparate applications. At least twice a week, his pager goes off during the wee hours of the morning because one of his programs has encountered this Oracle error:

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

Batch then has the unenviable task of phoning Totally’s Senior DBA, Don T. Planahead, and begging for a space quota increase. Don’s usual question is, “How much do you need?” to which Batch can only feebly reply, “I don’t know because the data load fluctuates so much.” This leaves them both very frustrated, because Don wants control over the space allocation for planning reasons, and Batch doesn’t want his night’s sleep interrupted so often.

Setting Up for the AFTER SUSPEND Trigger

Thankfully, an AFTER SUSPEND trigger can eliminate the dark circles under both Don’s and Batch’s eyes. Here is how they work through the situation.

Batch discovers the particular point in his code that encounters the error most frequently. It is an otherwise innocuous INSERT statement at the end of a program that takes hours to run:

INSERT INTO monthly_summary (

acct_no, trx_count, total_in, total_out)

VALUES (

v_acct, v_trx_count, v_total_in, v_total_out);

What makes this most maddening is that the values take hours to calculate, only to be immediately lost when the final INSERT statement fails. At the very least, Batch wants the program to suspend itself while he contacts Don to get more space allocated. He discovers that this can be done with a simple ALTER SESSION statement:

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'Monthly Summary';

This means that whenever this Oracle database session encounters an out-of-space error, it will go into a suspended (and potentially resumable) state for 3,600 seconds (1 hour). This provides enough time for Totally’s monitoring system to page Batch, for Batch to phone Don, and for Don to allocate more space. It’s not a perfect system, but at least the hours spent calculating the data are no longer wasted.

Another problem faced by Batch and Don is that when they try to diagnose the situation in the middle of the night, they are both so tired and grumpy that time is wasted on misunderstandings. Thankfully, the need for explanations can be alleviated by another feature of suspended/resumable statements: the DBA_RESUMABLE view. This shows all sessions that have registered for resumable statements with the ALTER SESSION command just shown.

NOTE

The RESUMABLE system privilege must be granted to users before they can enable the resumable option.

Now, whenever Batch’s programs go into the suspended state, he only has to phone Don and mumble, “Check the resumable view.” Don then queries it from his DBA account to see what is going on:

SQL> SELECT session_id,

2 name,

3 status,

4 error_number

5 FROM dba_resumable

SESSION_ID NAME STATUS ERROR_NUMBER

---------- -------------------- --------- ------------

8 Monthly Summary SUSPENDED 1536

1 row selected.

This shows that session 8 is suspended because of ORA-01536: space quota exceeded for tablespace <tablespace_name>. From past experience, Don knows which schema and tablespace are involved, so he corrects the problem and mumbles into the phone, “It’s fixed.” The suspended statement in Batch’s code immediately resumes, and both Don and Batch can go back to sleep in their own beds.

INVALID DDL OPERATION IN SYSTEM TRIGGERS

AFTER SUSPEND triggers are not allowed to actually perform certain DDL operations (ALTER USER and ALTER TABLESPACE) to fix the problems they diagnose. They simply raise the error ORA-30511: invalid DDL operation in system triggers. One way to work around this situation is as follows:

1. Have the AFTER SUSPEND trigger write the SQL statement necessary to fix a problem in a table.

2. Create a PL/SQL package that reads SQL statements from the table and executes them.

3. Submit the PL/SQL package to DBMS_JOB every minute or so.

Looking at the Actual Trigger

After a few weeks, both Don and Batch are tired of their repetitive (albeit abbreviated) late-night conversations, so Don sets out to automate things with an AFTER SUSPEND trigger. Here’s a snippet of what he cooks up and installs in the DBA account:

/* File on web: smart_space_quota.sql */

TRIGGER after_suspend

AFTER SUSPEND

ON DATABASE

DECLARE

...

BEGIN

-- if this is a space-related error...

IF ORA_SPACE_ERROR_INFO ( error_type => v_error_type,

object_type => v_object_type,

object_owner => v_object_owner,

table_space_name => v_tbspc_name,

object_name => v_object_name,

sub_object_name => v_subobject_name ) THEN

-- if the error is a tablespace quota being exceeded...

IF v_error_type = 'SPACE QUOTA EXCEEDED' AND

v_object_type = 'TABLE SPACE' THEN

-- get the username

OPEN curs_get_username;

FETCH curs_get_username INTO v_username;

CLOSE curs_get_username;

-- get the current quota for the username and tablespace

OPEN curs_get_ts_quota(v_object_name,v_username);

FETCH curs_get_ts_quota INTO v_old_quota;

CLOSE curs_get_ts_quota;

-- create an ALTER USER statement and send it off to

-- the fixer job because if we try it here we will raise

-- ORA-30511: invalid DDL operation in system triggers

v_new_quota := v_old_quota + 40960;

v_sql := 'ALTER USER ' || v_username || ' ' ||

'QUOTA ' || v_new_quota || ' ' ||

'ON ' || v_object_name;

fixer.fix_this(v_sql);

END IF; -- tablespace quota exceeded

END IF; -- space-related error

END;

This creates a trigger that fires whenever a statement enters a suspended state and attempts to fix the problem. (Note that this particular example handles only tablespace quotas being exceeded.)

Now when Batch’s programs encounter the tablespace quota problem, the database-wide AFTER SUSPEND trigger fires and puts a SQL entry in the “stuff to fix” table via the fixer package. In the background, a fixer job is running; it picks the SQL statement out of the table and executes it, thus alleviating the quota problem without requiring anyone to pick up the phone.

NOTE

A complete AFTER SUSPEND trigger and fixer package are available in the fixer.sql file on the book’s website.

The ORA_SPACE_ERROR_INFO Function

You can garner information on the cause of the statement suspension using the ORA_SPACE_ERROR_INFO function shown in earlier examples. Now let’s look at the syntax for specifying this function; the parameters are defined as shown in Table 19-3.

Table 19-3. ORA_SPACE_ERROR_INFO parameters

The function returns a Boolean value of TRUE if the suspension occurs because of one of the errors shown in the table, and FALSE if not.

The ORA_SPACE_ERROR_INFO function does not actually fix whatever space problems occur in your system; its role is simply to provide the information you need to take further action. In the earlier example, you saw how the quota error was addressed. Here are two additional examples of SQL you might supply to fix space problems diagnosed by the ORA_SPACE_ERROR_INFO function:

§ Specify the following when your table or index has achieved its maximum extents and no more extents are available:

ALTER object_type object_owner.object_name STORAGE (MAXEXTENTS UNLIMITED);

§ Specify the following when your tablespace is completely out of space:

§ /* Assume Oracle Managed Files (Oracle9i Database and later) being used so

§ explicit datafile declaration not required */

ALTER TABLESPACE tablespace_name ADD DATAFILE;

The DBMS_RESUMABLE Package

If the ORA_SPACE_ERROR_INFO function returns FALSE, then the situation causing the suspended statement cannot be fixed. Thus, there is no rational reason for remaining suspended. You can abort unfixable statements from within the AFTER_SUSPEND trigger using the ABORT procedure in the DBMS_RESUMABLE package. The following provides an example of issuing this procedure:

/* File on web: local_abort.sql */

TRIGGER after_suspend

AFTER SUSPEND

ON SCHEMA

DECLARE

CURSOR curs_get_sid IS

SELECT sid

FROM v$session

WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID');

v_sid NUMBER;

v_error_type VARCHAR2(30);

...

BEGIN

IF ORA_SPACE_ERROR_INFO(...

...try to fix things...

ELSE -- can't fix the situation

OPEN curs_get_sid;

FETCH curs_get_sid INTO v_sid;

CLOSE curs_get_sid;

DBMS_RESUMABLE.ABORT(v_sid);

END IF;

END;

The ABORT procedure takes a single argument, the ID of the session to abort. This allows ABORT to be called from a DATABASE- or SCHEMA-level AFTER SUSPEND trigger. The aborted session receives this error:

ORA-01013: user requested cancel of current operation

After all, the cancellation was requested by a user—but exactly which user is unclear.

In addition to the ABORT procedure, the DBMS_RESUMABLE package contains functions and procedures to get and set timeout values:

GET_SESSION_TIMEOUT

Returns the timeout value of the suspended session by session ID:

FUNCTION DBMS_RESUMABLE.GET_SESSION_TIMEOUT (sessionid IN NUMBER)

RETURN NUMBER;

SET_SESSION_TIMEOUT

Sets the timeout value of the suspended session by session ID:

PROCEDURE DBMS_RESUMABLE.SET_SESSION_TIMEOUT (sessionid IN NUMBER, TIMEOUT IN NUMBER);

GET_TIMEOUT

Returns the timeout value of the current session:

FUNCTION DBMS_RESUMABLE.GET_TIMEOUT RETURN NUMBER;

SET_TIMEOUT

Sets the timeout value of the current session:

PROCEDURE DBMS_REUSABLE.SET_TIMEOUT (TIMEOUT IN NUMBER);

NOTE

New timeout values take effect immediately but do not reset the counter to zero.

Trapped Multiple Times

AFTER SUSPEND triggers fire whenever a statement is suspended. Therefore, they can fire many times during the same statement. For example, suppose that the following hardcoded trigger is implemented:

/* File on web: increment_extents.sql */

TRIGGER after_suspend

AFTER SUSPEND ON SCHEMA

DECLARE

-- get the new max (current plus one)

CURSOR curs_get_extents IS

SELECT max_extents + 1

FROM user_tables

WHERE table_name = 'MONTHLY_SUMMARY';

v_new_max NUMBER;

BEGIN

- fetch the new maximum extent value

OPEN curs_get_extents;

FETCH curs_get_extents INTO v_new_max;

CLOSE curs_get_extents;

-- alter the table to take on the new value for maxextents

EXECUTE IMMEDIATE 'ALTER TABLE MONTHLY_SUMMARY ' ||

'STORAGE ( MAXEXTENTS ' ||

v_new_max || ')';

DBMS_OUTPUT.PUT_LINE('Incremented MAXEXTENTS to ' || v_new_max);

END;

If you start with an empty table with MAXEXTENTS (maximum number of extents) specified as 1, inserting four extents’ worth of data produces this output:

SQL> @test

Incremented MAXEXTENTS to 2

Incremented MAXEXTENTS to 3

Incremented MAXEXTENTS to 4

PL/SQL procedure successfully completed.

To Fix or Not to Fix?

That is the question! The previous examples have shown how “lack of space” errors can be handled on the fly by suspending statements until intervention (human or automated) allows them to continue. Taken to an extreme, this approach allows applications to be installed with minimal tablespace, quota, and extent settings, and then to grow as required. While over-diligent DBAs may see this situation as nirvana, it does have its downsides:

Intermittent pauses

Suspended statement pauses may wreak havoc with high-volume online transaction processing (OLTP) applications that require high throughput levels. This will be even more troublesome if the fix takes a long time.

Resource contention

Suspended statements maintain their table locks, which may cause other statements to wait for long periods of time or fail needlessly.

Management overhead

The resources required to continuously add extents or datafiles or increment quotas may wind up overwhelming those required to actually run the application.

For these reasons, I recommend that AFTER SUSPEND triggers be used judiciously. They are perfect for long-running processes that must be restarted after failure, as well as for incremental processes that require DML to undo their changes before they can be restarted. However, they are not well suited to OLTP applications.

Maintaining Triggers

Oracle offers a number of DDL statements that can help you manage your triggers. As explained in the following sections, you can enable, disable, and drop triggers; view information about triggers; and check the status of triggers.

Disabling, Enabling, and Dropping Triggers

Disabling a trigger causes it not to fire when its triggering event occurs. Dropping a trigger causes it to be removed from the database altogether. The SQL syntax for disabling triggers is relatively simple compared to that for creating them:

ALTER TRIGGER trigger_name DISABLE;

For example:

ALTER TRIGGER emp_after_insert DISABLE;

A disabled trigger can also be reenabled, as shown in the following example:

ALTER TRIGGER emp_after_insert ENABLE;

The ALTER TRIGGER command is concerned only with the trigger name; it does not require identifying the trigger type or anything else. You can also easily create stored procedures to handle these steps for you. The following procedure, for example, uses dynamic SQL to disable or enable all triggers on a table:

/* File on web: settrig.sp */

PROCEDURE settrig (

tab IN VARCHAR2

, sch IN VARCHAR DEFAULT NULL

, action IN VARCHAR2

)

IS

l_action VARCHAR2 (10) := UPPER (action);

l_other_action VARCHAR2 (10) := 'DISABLED';

BEGIN

IF l_action = 'DISABLE'

THEN

l_other_action := 'ENABLED';

END IF;

FOR rec IN (SELECT trigger_name FROM user_triggers

WHERE table_owner = UPPER (NVL (sch, USER))

AND table_name = tab AND status = l_other_action)

LOOP

EXECUTE IMMEDIATE

'ALTER TRIGGER ' || rec.trigger_name || ' ' || l_action;

END LOOP;

END;

The DROP TRIGGER command is just as easy; simply specify the trigger name, as shown in this example:

DROP TRIGGER emp_after_insert;

Creating Disabled Triggers

Starting with Oracle Database 11g, it is possible to create triggers in a disabled state so they don’t fire. This is very helpful in situations where you want to validate a trigger but don’t want it to start firing just yet. Here’s a very simple example:

TRIGGER just_testing

AFTER INSERT ON abc

DISABLE

BEGIN

NULL;

END;

Because the DISABLE keyword is included in the header, this trigger gets validated, compiled, and created, but it will not fire until it is explicitly enabled later on. Note that the DISABLE keyword is not present in what gets saved into the database, though:

SQL> SELECT trigger_body

2 FROM user_triggers

3 WHERE trigger_name = 'JUST_TESTING';

TRIGGER_BODY

-----------------------------------------

BEGIN

NULL;

END;

When you are using a GUI tool, be careful to avoid accidentally enabling triggers when they are recompiled.

Viewing Triggers

You can find out lots of information about triggers by issuing queries against the following data dictionary views:

DBA_TRIGGERS

All triggers in the database

ALL_TRIGGERS

All triggers accessible to the current user

USER_TRIGGERS

All triggers owned by the current user

Table 19-4 summarizes the most useful (and common) columns in these views.

Table 19-4. Useful columns in trigger views

Checking the Validity of Triggers

Oddly enough, the trigger views in the data dictionary do not display whether or not a trigger is in a valid state. If a trigger is created with invalid PL/SQL, it is saved in the database but marked as INVALID. You can query the USER_OBJECTS or ALL_OBJECTS views to determine this status, as shown here:

SQL> CREATE OR REPLACE TRIGGER invalid_trigger

2 AFTER DDL ON SCHEMA

3 BEGIN

4 NULL

5 END;

6 /

Warning: Trigger created with compilation errors.

SQL> SELECT object_name,

2 object_type,

3 status

4 FROM user_objects

5 WHERE object_name = 'INVALID_TRIGGER';

OBJECT_NAME OBJECT TYPE STATUS

------------- ----------- -------

INVALID_TRIGGER TRIGGER INVALID