16-1.

A trigger is a block of code (whether in PL/SQL, Java, or C) that fires, or executes, in response to a specific event.

16-2.

The statements are:

16-3.

A trigger executes implicitly in response to an event, such as an update to a table. A procedure executes explicitly at the request of a user (or a call from another program).

16-4.

A trigger can have one of two modes: ENABLED (meaning that it fires normally) and DISABLED (meaning that it does not fire, even if its triggering event occurs).

16-5.

(b). A trigger that causes other triggers to fire is called a cascade.

16-6.

Statement-level triggers fire only one time per statement, and row-level triggers fire for each row that is affected by the DML statement.

16-7.

The WHEN clause causes a trigger to fire only when a specific set of user-defined conditions are met.

16-8.

Trigger (b) correctly populates the employee_id column. Trigger (a), which attempts to set the sequence number using an INSERT statement, illustrates a fairly common mistake. While Trigger (a) compiles successfully, it will probably produce the following error when it’s executed:

ORA-00036: Maximum number of recursive sql levels (50)

This error is generated because each execution of the trigger results in another execution of the same trigger, eventually exceeding the number of allowed recursive SQL levels. This limit was introduced to prevent such mistaken constructions from resulting in an infinite loop.

16-9.

The pseudo-columns shown are:

The OLD, NEW, and PARENT pseudo-columns can be used only in row-level triggers. PARENT, introduced in Oracle8i, refers to the current row of the parent table for a trigger defined on a nested table. OLD and NEW refer to the following:

16-10.

You must have one of the following privileges:

16-11.

The ALTER ANY TRIGGER privilege allows you only to enable/disable a trigger. The CREATE ANY TRIGGER privilege allows you to create a new trigger in any schema or recreate an existing trigger without having to explicitly drop it and then create it again.

When a trigger is created, it is compiled and stored in the database. If you want to change the text of the trigger, you cannot just “edit” a piece of code; you have to change the definition in the database. To do this, you have to either use the DROP TRIGGER and CREATE TRIGGER commands or the CREATE OR REPLACE TRIGGER command.

16-12.

The error occurs in the WHEN clause; when referring to the new or old values in a WHEN clause, you must omit the colon:

CREATE OR REPLACE TRIGGER emp_before_ins_t
   BEFORE INSERT
   ON employee
   FOR EACH ROW
   WHEN (NEW.mgr is null)
BEGIN
   IF (:NEW.sal > 800)
   THEN
      :NEW.sal := 850;
   END IF;
END;

Also, note that the WHEN condition must be a SQL condition, rather than a PL/SQL condition. PL/SQL functions or methods cannot be invoked from the trigger WHEN clause.

16-13.

When Oracle compiles the trigger definition, it parses the entire code and reports all the errors encountered during the compilation phase; you can see these errors by querying the trusty USER_ERRORS data dictionary view:

SELECT line, position, text
  FROM user_errors
 WHERE name = 'MY_TRIGGER'
   AND TYPE = 'TRIGGER'
 ORDER BY sequence

In SQL*Plus, you can also use the following shortcut:

SQL> SHOW ERRORS TRIGGER MY_TRIGGER

16-14.

There is only one way to explicitly recompile a trigger: execute the ALTER command with the COMPILE option:

ALTER TRIGGER my_trigger_name COMPILE

You might have to use this command because triggers, as part of the Oracle object dependency, may become invalid if an object that the trigger depends upon changes.

16-15.

You might want to omit the OR REPLACE option when you first create a trigger to save yourself a headache if a trigger by that name already exists in that schema. Since you’re not using the OR REPLACE option, if a trigger already exists by that name, you get the following error:

ORA-04081: trigger name already exists

16-16.

The statements are:

16-17.

The statements are:

16-18.

(c). Here is the required syntax:

CREATE OR REPLACE TRIGGER upd_employee_commision
   AFTER UPDATE OF comm
   ON emp
   FOR EACH ROW
BEGIN
   <<Trigger logic>>
END;

16-19.

(d). The trigger already fires just once for each INSERT operation on the emp table.

16-20.

The trigger fails because the statement-level trigger cannot reference a pseudo-column name such as :NEW or :OLD. This is only available for row-level triggers.

16-21.

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint. The SQL statements of a trigger cannot read from (query) or modify a mutating table of the triggering statement.

16-22.

Executing the procedure results in an error because DBMS_SQL treats :OLD and :NEW as ordinary bind variables; consequently, it complains that not all the binds have been assigned. In short, the :OLD and :NEW predicates can’t be directly referenced in statements executed via dynamic SQL.

16-23.

The following template shows how to raise an error when a DML statement violates a business rule:

/* Filename on web page: secure_del_trigger.sql */
CREATE OR REPLACE TRIGGER secure_del_trigger
   BEFORE DELETE
   ON emp
   FOR EACH ROW
DECLARE
   unauthorized_deletion EXCEPTION;
BEGIN
   IF <your business rule is violated> THEN
     RAISE unauthorized_deletion;
   END IF;
EXCEPTION
   WHEN unauthorized_deletion
   THEN
      raise_application_error (-20500,
        'This record cannot be deleted'),
END;
/

16-24.

(b). Object privileges must be granted directly to the trigger owner and cannot be acquired through database roles.

16-25.

The triggers are:

16-26.

No. The restriction still applies because a stored procedure, even though it’s called by a trigger, still runs within the trigger’s transaction context.

16-27.

You can use Oracle8i’s autonomous transaction pragma to start a new context. The following trigger illustrates how to use dynamic SQL to execute a DDL statement, which requires an implicit commit, inside a trigger:

/* Filename on web page: format_table_trig.sql */
CREATE OR REPLACE TRIGGER format_table_trig
   AFTER INSERT
   ON format_table
   FOR EACH ROW
   WHEN (new.tablecode = 3334)
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   seq_sql         VARCHAR(200);
   cursor_handle   INTEGER;
   execute_ddl     INTEGER;
BEGIN
   seq_sql := 'CREATE SEQUENCE ' ||
              SUBSTR (:new.table_id, 1, 21) ||
              '_SEQ START WITH 0 INCREMENT BY 1 MINVALUE 0';
   cursor_handle := DBMS_SQL.open_cursor;
   DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native);
   execute_ddl := DBMS_SQL.execute (cursor_handle);
   DBMS_SQL.close_cursor (cursor_handle);
END;

Prior to Oracle8i, you needed to use database pipes (via the DBMS_PIPE package) to achieve this sort of transaction isolation. You first wrote a program to create the sequence (or create a record into a logging table), started it in another process/session, and then used DBMS_PIPE to send it requests from inside your trigger. Since the procedure in the other session was completely isolated from the main transaction, you circumvented the restrictions on transactions inside a trigger. Of course, this method was a lot more work!

16-28.

The statements are:

16-29.

The following SQL*Plus script enables or disables all the triggers in the current user’s schema:

/* Filename on web page: set_trigger_status.sql */
SET VERIFY OFF;
SET SERVEROUTPUT ON;

PROMPT  Program to enable/disable user triggers
ACCEPT op PROMPT '(E - enable, D - disable): '

DECLARE
   cur INTEGER;
   done EXCEPTION;
   cnt NUMBER := 0;
BEGIN
   FOR user_trg IN (SELECT trigger_name
                      FROM user_triggers)
   LOOP
      BEGIN
         cnt := cnt + 1;
         cur := DBMS_SQL.open_cursor;

         IF UPPER ('&&op') = 'E'
         THEN
            DBMS_SQL.parse (
               cur,
               'ALTER TRIGGER  ' ||
               user_trg.trigger_name ||
               ' ENABLE',
               DBMS_SQL.native
            );
         ELSIF UPPER ('&&op') = 'D'
         THEN
            DBMS_SQL.parse (
               cur,
               'ALTER TRIGGER ' ||
               user_trg.trigger_name ||
               ' DISABLE',
               DBMS_SQL.native
            );
         ELSE
            DBMS_OUTPUT.put_line (
               'Invalid input argument passed'
            );
            DBMS_SQL.close_cursor (cur);
            RETURN;
         END IF;

         DBMS_SQL.close_cursor (cur);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
               SQLCODE || '-' || SQLERRM
            );
            DBMS_SQL.close_cursor (cur);
      END;
   END LOOP;

   IF UPPER ('&&op') = 'E'
   THEN
      DBMS_OUTPUT.put_line (
         cnt || ' triggers enabled'
      );
   ELSIF UPPER ('&&op') = 'D'
   THEN
      DBMS_OUTPUT.put_line (
         cnt || ' triggers disabled'
      );
   END IF;
END;
/

16-30.

Triggers of the same type fire in the order of their respective object identifiers (OIDs). OIDs, which are assigned by Oracle when the trigger is created, are beyond the designer’s control. Consequently, the order of firing triggers is not guaranteed and cannot be controlled. The best approach is to make sure that the trigger design is independent of the order of trigger firing.

16-31.

The trigger is clearly designed to fire for DML update events and only when the new salary doesn’t equal the old salary. The way the trigger is written at present, it fires unnecessarily across a wide range of DML events that occur on the employee table. You can use the WHEN clause to eliminate these unnecessary executions:

CREATE OR REPLACE TRIGGER employee_upd_t1
   AFTER UPDATE OF salary
   ON employee
   FOR EACH ROW
   WHEN (old.salary <> new.salary)
BEGIN
   employee_pkg.update_emp (:new.employee_id, :new.salary);
END;

16-32.

At first, you might be tempted to try something like this:

CREATE OR REPLACE TRIGGER employee_t1
   BEFORE DELETE
   ON employee
   FOR EACH ROW
BEGIN
  UPDATE employee
    SET mgr = null
  WHERE mgr = :new.empno;
END;
/

Unfortunately, this trigger results in the mutating trigger error:

ORA-04091 table name is mutating, trigger/function may not see it

You can use a combination of packaged variables and different types of triggers to solve this problem.

The first step is to create a package containing an index-by table to hold the IDs of the managers who have been deleted:

CREATE OR REPLACE PACKAGE mutating_table_pkg
IS
   TYPE array IS TABLE OF emp%ROWTYPE
      INDEX BY BINARY_INTEGER;

   emp_values   array;
   empty        array;
END;
/

The second step is to create a statement-level BEFORE DELETE trigger that fires at the beginning of the transaction; its only purpose is to initialize the emp_values table to make sure it is empty:

CREATE OR REPLACE TRIGGER mutating_trig_1
   BEFORE DELETE
   ON emp
BEGIN
   mutating_table_pkg.emp_values := mutating_table_pkg.empty;
END;
/

The third step is to create a row-level BEFORE UPDATE trigger to populate the emp_values tables with the employee numbers of the rows that are being deleted. This is the only type of processing implemented in this trigger; the UPDATE statement is intentionally removed from this trigger because it caused the “mutating table” problem in the first place:

CREATE OR REPLACE TRIGGER mutating_trig_2
   BEFORE DELETE
   ON emp
   FOR EACH ROW
   WHEN (old.job = 'MANAGER')
DECLARE
   i   NUMBER := mutating_table_pkg.emp_values.COUNT + 1;
BEGIN
   mutating_table_pkg.emp_values (i).empno := :old.empno;
END;
/

The final step is to create a statement-level AFTER DELETE that uses the array of managers to modify the employee records. At this point, the employee table is no longer a mutating table (undergoing changes), so you’re free to make update statements:

CREATE OR REPLACE TRIGGER mut_trg_3
   AFTER DELETE
   ON emp
BEGIN
   FOR i IN 1 .. mutating_table_pkg.emp_values.COUNT
   LOOP
      UPDATE emp
         SET mgr = NULL
       WHERE mgr = mutating_table_pkg.emp_values (i).empno;
   END LOOP;
END;
/

16-33.

As of Oracle8i, the only way to log in to a database that has an invalid AFTER LOGON trigger is to use a DBA utility that can CONNECT INTERNAL (e.g., Server Manager). Here’s a trace of a SVRMGR session to disable the trigger:

SVRMGR> connect internal
Connected.
SVRMGR> ALTER TRIGGER on_logon DISABLE;
Statement processed.

16-34.

Because the trigger is running as an autonomous transaction, the aggregate query on the emp tables doesn’t see the rows inserted by the calling transaction. Consequently, the trigger doesn’t correctly record the department’s salary history.

16-35.

The first trigger, which is governed by the local object dependency mechanism provided by Oracle, is invalidated immediately after the UPDATE_BONUS procedure is recompiled. Since it’s recompiled and revalidated automatically, the next execution of the trigger fires successfully.

The second trigger, which refers to a remote procedure, is not immediately invalidated and revalidated after UPDATE_BONUS@RDB is recompiled. Consequently, the trigger produces the following stack of errors:

ORA-04068: existing state of packages has been discarded
ORA-04062: timestamp of procedure "UPDATE_BONUS" has been changed
ORA-06512: at "REM_PROC_TRIGGER", line 2
ORA-04088: error during execution of trigger 'REM_PROC_TRIGGER'