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

INSERT INTO to_table
SELECT * FROM from_table;

INSERT INTO to_table
SELECT * FROM from_table;

INSERT INTO to_table
 VALUES (1);
UPDATE to_table
 SET col1 = 10;
DELETE to_table;

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

1    CREATE [OR REPLACE] TRIGGERtrigger 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:

CREATE OR REPLACE TRIGGER check_raise
   AFTER UPDATE OF salary
   ON employee
   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
   ...

CREATE OR REPLACE TRIGGER valid_when_clause
BEFORE INSERT ON frame
FOR EACH ROW
WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 )
   ...

SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when
  2 BEFORE INSERT ON frame
  3 WHEN ( new.strike = 'Y' )
  4 FOR EACH ROW
  5 BEGIN NULL; END;
  8  /

ERROR at line 3:
ORA-04077: WHEN clause cannot be used with table level triggers

SQL> CREATE OR REPLACE TRIGGER row_must_be_before_when
  2  BEFORE INSERT ON frame
  3  FOR EACH ROW
  4  WHEN ( new.strike = 'Y' )
  5  BEGIN NULL; END;
  8  /

Trigger created.

CREATE OR REPLACE 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,
                :after_cheat.frame_number,
                :prior_to_cheat.score,
                :after_cheat.score,
                SYSDATE,
                'UPDATE'),
END;

/* File on web: one_trigger_does_it_all.sql */
CREATE OR REPLACE 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;

/* File on web: overloaded_update.sql */
CREATE OR REPLACE 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;

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 Bowl-A-Rama 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 */
CREATE 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:

CREATE 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    CREATE OR REPLACE 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 only has 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.

CREATE OR REPLACE 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;

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

/* File on web: final_audit.sql */
CREATE OR REPLACE 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;

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

CREATE OR REPLACE TRIGGER set_score
BEFORE INSERT ON frame
FOR EACH ROW
WHEN ( NEW.score IS NULL )
BEGIN
  IF :NEW.strike = 'Y' OR :NEW.spare = 'Y'
  THEN
    :NEW.score := :NEW.score + 10;
  END IF;
END;

/* File on web: validate_score.sql */
CREATE OR REPLACE 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;

SQL>INSERT INTO frame VALUES(1,1,1,'Y',NULL,5);
INSERT INTO frame
            *
ERROR at line 1:
ORA-20001: ERROR: Score For Strike Must >= 10

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 of 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 */
CREATE OR REPLACE TRIGGER golf_commentary
BEFORE INSERT ON golf_scores
FOR EACH ROW
BEGIN
  IF :NEW.score < 72 THEN
    :NEW.commentary := 'Under Par';
  ELSIF :NEW.score = 72 THEN
       :NEW.commentary := 'Par';
  ELSE
    :NEW.commentary := 'Over Par';
  END IF;
END;

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

CREATE OR REPLACE TRIGGER golf_commentary_under_par
BEFORE INSERT ON golf_scores
FOR EACH ROW
WHEN (NEW.score < 72)
BEGIN
  :NEW.commentary := 'Under Par';
END;

CREATE OR REPLACE TRIGGER golf_commentary_par
BEFORE INSERT ON golf_scores
FOR EACH ROW
WHEN (NEW.score = 72)
BEGIN
  :NEW.commentary := 'Par';
END;

CREATE OR REPLACE 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.

One pitfall of multiple triggers is that there is no guarantee of the order in which they will fire. While this is not a concern in the above example, it could be a problem in others, as shown next.

What values will be shown by the final query?

/* File on web: multiple_trigger_seq.sql */
DROP TABLE incremented_values;

CREATE TABLE incremented_values
(value_inserted    NUMBER,
 value_incremented NUMBER);

CREATE OR REPLACE TRIGGER increment_by_one
BEFORE INSERT ON incremented_values
FOR EACH ROW
BEGIN
  :NEW.value_incremented := :NEW.value_incremented + 1;
END;
/

CREATE OR REPLACE 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? There is no guarantee. Will this result always be received? Again, there is no guarantee. Oracle explicitly states that there is no way to control or assure the order in which multiple triggers of the same type on a single table will 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 should not be relied upon.

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 employee 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:

CREATE OR REPLACE TRIGGER brake_on_raises
   BEFORE UPDATE OF salary ON employee
   FOR EACH ROW
DECLARE
   l_curr_max NUMBER;
BEGIN
   SELECT MAX (salary) INTO l_curr_max
     FROM employee;
   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:

Because each release of Oracle 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 web site.

In addition, the file mutating_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.

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

1    CREATE [OR REPLACE] TRIGGERtrigger 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.

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;
                  /
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 above 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.

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

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

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.

Note the following about the event and attribute functions:

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:

CREATE OR REPLACE TRIGGER no_create
   AFTER CREATE ON SCHEMA
BEGIN
   RAISE_APPLICATION_ERROR (
      -20000,
      'ERROR : Objects cannot be created in the production database.'
   );
END;

After installing 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?

/* File on web: no_create.sql */
CREATE OR REPLACE 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:

CREATE OR REPLACE 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;

/* File on web: preserve_app_cols.sql  */
CREATE OR REPLACE 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
               RAISE_APPLICATION_ERROR (
                  '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;

IF ORA_IS_DROP_COLUMN ('COL2')
THEN
  do something!
ELSE
  do something else!
END IF;

/* File on web: what_privs.sql */
CREATE OR REPLACE 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;

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

We have shown that one use for DDL triggers is preventing a particular type of DDL on a particular object or type of object. But what if we create a trigger that prevents DROP DDL and then attempt to drop the trigger itself? Will we 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.

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.

CREATE 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 shown above, 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

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

1    CREATE [OR REPLACE] TRIGGERtrigger 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:

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.

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;

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;

AFTER LOGON triggers fire when an Oracle 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:

CREATE OR REPLACE 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;

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:

CREATE OR REPLACE TRIGGER before_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
  gather_session_stats;
END;

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

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:

CREATE OR REPLACE TRIGGER error_echo
AFTER SERVERERROR
ON SCHEMA
BEGIN
  DBMS_OUTPUT.PUT_LINE ('You experienced an error'),
END;

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

/* File on web: error_log.sql */
CREATE OR REPLACE 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;

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

-- 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;

CREATE OR REPLACE TRIGGER error_log
AFTER SERVERERROR
ON DATABASE
BEGIN
  central_error_log.log_error;
END;

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

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;

Total Found   = 1
Min Timestamp = 04-JAN-02
Max Timestamp = 04-JAN-02

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

1    CREATE [OR REPLACE] TRIGGERtrigger_name
2    INTEAD OF operation
3    ON view name
4    FOR EACH ROW
5    BEGIN
6      ... code goes here ...
7    END;

The 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 we can start pounding the dough, we have to put a system in place to monitor our deliveries. We will need three tables: one to track actual deliveries, one to track delivery areas, and one to track our 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.

We will also need three sequences to provide unique identifiers for our 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 our employees, we will simplify deliveries into a single view displaying delivery, area, and driver information:

CREATE OR REPLACE 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 our system relies heavily on this view for query functionality, why not make it available for insert, update, and delete as well? We cannot directly issues DML statements against the view; it is a join of multiple tables. How would Oracle know what to do with an INSERT ? In fact, we need to tell Oracle very explicitly what to do when an insert, update, or delete operation occurs against the delivery_info view; in other words, we need to tell it what to do instead of trying to insert, update, or delete. Thus, we will use INSTEAD OF triggers. Let’s start with the INSERT trigger.

Our INSERT trigger will perform four basic operations:

Bear in mind that this example is intended to demonstrate triggers—not how to effectively build a business system! After a while we 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 */
CREATE OR REPLACE 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;

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

CREATE OR REPLACE 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 DELETE trigger is the simplest of all. It merely ensures that we are not deleting a completed entry and then removes the delivery record. The driver and area records remain intact.

CREATE OR REPLACE 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;

Now, with a single INSERT focused on the delivery information we 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

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 below.

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

CREATE OR REPLACE 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, Oracle 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.

CREATE OR REPLACE 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:

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 a 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 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 shown above.

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.

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 */
CREATE OR REPLACE 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.

Information on the cause of the statement suspension may be garnered 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.

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, we 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:

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. Unfixable statements can be aborted 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 */
CREATE OR REPLACE 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  -- cant 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:

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 */
CREATE OR REPLACE 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.

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 down sides:

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.

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 TRIGGERtrigger_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 */
 
CREATE OR REPLACE 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;

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

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

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