Search the Catalog
Oracle PL/SQL Developer's Workbook

Oracle PL/SQL Developer's Workbook

By Steven Feuerstein with Andrew Odewahn
1st Edition May 2000
1-56592-674-9, Order Number: 6749
640 pages, $36.95

Chapter 16, Solutions
Triggers

Beginner

  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.
  2. The statements are:
    1. True. You can create a trigger in response to most Data Manipulation Language (DML) operations.
    2. False. There is no AFTER EXECUTION.
    3. True. Oracle8i allows you to trap "user" level events such as logons.
    4. True. You can create triggers that fire when Data Definition Language (DDL) statements are executed.

  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).
  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).
  5. (b). A trigger that causes other triggers to fire is called a cascade.
  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.
  7. The WHEN clause causes a trigger to fire only when a specific set of user-defined conditions are met.
  8. Trigger 2 correctly populates the employee_id column. Trigger 1, which attempts to set the sequence number using an INSERT statement, illustrates a fairly common mistake. While Trigger 1 compiles successfully, it will probably produce the following error when it's executed:
  9. 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.

  10. The pseudo-columns shown are:
    1. Valid.
    2. Invalid.
    3. Invalid.
    4. Valid.
    5. Valid.
    6. Invalid.
    7. Invalid.

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

    TIP:  

    OLD and NEW are the default names; specifying the REFERENCING clause of the trigger can change these names.

  11. You must have one of the following privileges:
  12. CREATE TRIGGER
    Allows you to create a trigger in your own schema for a table or view owned by the same schema

    CREATE ANY TRIGGER
    Allows you to create a trigger in any user's schema on a table owned by any schema

    ADMINISTER DATABASE TRIGGER
    Allows you to create database level triggers (e.g., SERVERERROR, LOGON, LOGOFF, etc.)

  13. 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.
  14. 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.

  15. The error occurs in the WHEN clause; when referring to the new or old values in a WHEN clause, you must omit the colon:
  16. 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.

  17. 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:
  18. 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

    Remember that if a trigger compiles with errors, it still gets created but fails during the execution. This means that all triggering DML statements are blocked until the trigger is temporarily disabled, dropped, or replaced with the trigger version that compiles without errors.
  19. There is only one way to explicitly recompile a trigger: execute the ALTER command with the COMPILE option:
  20. 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.

  21. 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:
  22. ORA-04081: trigger name already exists

  23. The statements are:
    1. Incorrect. Oracle explicitly disallows developers from putting triggers on SYS data dictionary objects, because this could inadvertently modify the behavior of the database. If an attempt is made to create a trigger on a SYS object, Oracle generates the error "ORA-04089: cannot create triggers on objects owned by SYS."
    2. Correct. See (a).
    3. Incorrect. Prior to Oracle8i, DML events were the only events that could be trapped by triggers. Oracle8i introduced the ability to trap other events as well, such as database-level events (STARTUP, SHUTDOWN, etc.) and DDL events (CREATE, DROP, etc.).

  24. The statements are:
    1. False. You can create triggers only for certain types of schema objects.
    2. True. Oracle7 allowed the triggers to be created at the TABLE level.
    3. False. Oracle8 added the ability to define triggers for VIEWs, which opened the road to fully updateable views in Oracle.
    4. Oracle8i introduced the triggers created on NESTED TABLE level.
    5. True. As of Oracle8i, you can also use the ON clause to define triggers for database or schema-level events. The DATABASE keyword specifies that the trigger is defined for the entire database, and the SCHEMA keyword specifies that the trigger is defined for the current schema.

  25. (c). Here is the required syntax:
  26. CREATE OR REPLACE TRIGGER upd_employee_commision
       AFTER UPDATE OF comm
       ON emp
       FOR EACH ROW
    BEGIN
       <<Trigger logic>>
    END;
    
  27. (d). The trigger already fires just once for each INSERT operation on the emp table.
  28. 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.
  29. 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.

Intermediate

  1. 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.
  2. The following template shows how to raise an error when a DML statement violates a business rule:
  3. 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;
    /
    
  4. (b). Object privileges must be granted directly by the owner and cannot be acquired through database roles.
  5. The triggers are:
    1. Invalid. The trigger explicitly calls SQL COMMIT, which is forbidden in Oracle.
    2. Invalid. The trigger dynamically creates a sequence via the DDL statement CREATE SEQUENCE that is issuing an implicit commit. SQL DDL statements are not allowed in triggers because DDL statements issue implicit COMMITS upon completion.
    3. Valid. A system trigger is the only type of trigger that allows CREATE/ALTER/DROP TABLE statements and ALTER...COMPILE in the trigger body, despite the fact it issues an implicit COMMIT.

  6. 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.
  7. 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:
  8. 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!

  9. The statements are:
    1. False. A trigger can contain more than 32 lines of code.
    2. True. A trigger's size is limited to 32K.
    3. True. Only 32 triggers can cascade at one time.
    4. True. The maximum size of a string that can hold a RAW or LONG RAW is 32K.

  10. The following SQL*Plus script enables or disables all the triggers in the current user's schema:
  11. /* 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;
    /
    

Expert

  1. 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.
  2. 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 occurs on the employee table. You can use the WHEN clause to eliminate these unnecessary executions:
  3. 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;
    
  4. At first, you might be tempted to try something like this:
  5. 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;
    /
    
  6. As of Oracle8iOracle8i, 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:
  7. SVRMGR> connect internal 
    Connected. 
    SVRMGR> ALTER TRIGGER on_logon DISABLE; 
    Statement processed.
    
  8. 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.
  9. 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.
  10. 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'
    

Back to: Sample Chapter Index

Back to: Oracle PL/SQL Developer's Workbook


O'Reilly Home | O'Reilly Bookstores | How to Order | O'Reilly Contacts
International | About O'Reilly | Affiliated Companies

© 2001, O'Reilly & Associates, Inc.
webmaster@oreilly.com