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, Problems
Triggers

A trigger is a special PL/SQL procedure that fires, or executes, in response to a specific triggering event. For example, you might write a trigger to enforce a business rule on INSERT statements on a particular table, maintain referential integrity in a distributed database, or track user logons. A trigger has three parts:

This chapter tests your ability to (among other things) define triggers for a variety of events, work with correlation variables (special pseudo-column names that represent things like the old and new values of a column), and use triggers to implement business logic.

Beginner

  1. What is a trigger?
  2. For which of the following events can you create a trigger?
    1. An INSERT, UPDATE, or DELETE statement on a specific object
    2. An execution of a specific procedure
    3. A user logon (or logoff)
    4. A DDL statement, such as DROP or ALTER, on a specific object

  3. What is one of the main differences between the execution of a trigger and the execution of a stored procedure?
  4. What are the two modes a trigger can have?
  5. Which of the following terms describes the situation in which the execution of one trigger results in the execution of another, or possibly more, different triggers?
    1. Trigger torrent
    2. Cascading triggers
    3. Chain reaction
    4. Interlock
    5. Recursive nesting

  6. What is the difference between a statement-level trigger and a row-level trigger?
  7. What clause makes a trigger fire only when a specific condition is true?
  8. Which of the following triggers populates the employee_id column of the employee table with the next employee_seq sequence value?
    1. CREATE OR REPLACE TRIGGER employee_ins_t1 BEFORE INSERT ON employee FOR EACH ROW BEGIN INSERT INTO employee (employee_id) VALUES (employee_seq.nextval); END;

    2. CREATE OR REPLACE TRIGGER employee_ins_t1 BEFORE INSERT ON employee FOR EACH ROW BEGIN SELECT employee_seq.nextval INTO :new.employee_id FROM dual; END;

  9. Which of these special "pseudo" records are allowed inside a trigger?
    1. :NEW
    2. :CURRENT
    3. :OLDEST
    4. :PARENT
    5. :OLD
    6. :NEWEST
    7. :RECORD

  10. What system privileges are required to create a trigger?
  11. What is the difference between the ALTER ANY TRIGGER privilege and the CREATE ANY TRIGGER privilege?
  12. Why does the following trigger generate a "ORA-00920: invalid relational operator" error?
  13. 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;
    
  14. How can you view a trigger's compilation errors?
  15. How many different ways can you recompile a trigger?
  16. Why might you want to omit the OR REPLACE clause when you first create a trigger?
  17. Which of the following statements are correct, and which are incorrect?
    1. A user can create a trigger in any database schema if she has the CREATE ANY TRIGGER privilege.
    2. A user can create a trigger in any database schema (with the exception of SYS) if she has the CREATE ANY TRIGGER privilege.
    3. Triggers can trap only DML events such as INSERT, DELETE, and UPDATE.

  18. True or false? You can define a trigger for:
    1. Any schema-level object
    2. A table
    3. A view
    4. Any nested table
    5. The entire database or a user schema

  19. Examine the following trigger:
  20. CREATE OR REPLACE TRIGGER upd_employee_commision
    FOR EACH ROW
    BEGIN
       <<Trigger logic>>
    END;
    

    Which of the following statements must you add to the trigger definition to make sure this trigger executes only after updating the comm column of the emp table?

    1. AFTER UPDATE(comm) ON emp
    2. AFTER UPDATE ON emp
    3. AFTER UPDATE OF comm ON emp
    4. AFTER comm UPDATE ON emp

  21. Examine the following trigger:
  22. CREATE OR REPLACE TRIGGER insert_employee
      AFTER INSERT ON emp
    BEGIN
       <<Trigger logic>>
    END;
    

    Which of the following statements must you add to the trigger definition to make sure it executes only once for each INSERT operation on the emp table?

    1. FOR EVERY ROW
    2. WHEN (new.sal IS NULL)
    3. FOR EACH ROW
    4. No modifications are necessary

  23. Why does the following trigger fail when it's executed?
  24. CREATE OR REPLACE TRIGGER ins_emp_summary
       AFTER INSERT
       ON emp
    BEGIN
       INSERT INTO emp_summary (empno, period, ytd_salary)
            VALUES (:new.empno, SYSDATE, :new.sal);
    END;
    
  25. What is a mutating table?

Intermediate

  1. What happens when the following trigger executes?
  2. CREATE OR REPLACE TRIGGER employee_ins_t1
       BEFORE UPDATE
       ON employee
       FOR EACH ROW
    DECLARE
       cur    PLS_INTEGER    := DBMS_SQL.open_cursor;
       fdbk   PLS_INTEGER;
       stmt   VARCHAR2(2000);
    BEGIN
       stmt := 'BEGIN IF :old.' ||
               emp_pkg.col_name ||
               '= ' ||
               emp_pkg.col_value ||
               '''' ||
               ' THEN  :new.salary := :new.salary * 2; ' ||
               ' END IF;' ||
               ' END; ';
       DBMS_SQL.parse (cur, sql_stmt, DBMS_SQL.native);
       fdbk := DBMS_SQL.execute (cur);
    END;
    
  3. Provide a template for a trigger that raises an error when a client application violates a business rule (e.g., a trigger that raises an error if a user attempts to delete a row from the employee table).
  4. You want to issue DML statements or execute PL/SQL stored programs inside a trigger. Which of the following describes how you must grant the necessary privileges on the underlying object?
    1. Privileges on the underlying object must be granted through the database roles.
    2. Privileges on the underlying object must be granted directly from the user who owns the object.
    3. Privileges can be granted either through database roles or directly from the user who owns the object.

  5. Indicate whether the following triggers are valid or invalid (a valid procedure both compiles and executes without error):
    1. CREATE OR REPLACE TRIGGER emp_audit_trg BEFORE INSERT OR UPDATE ON employee FOR EACH ROW BEGIN IF (inserting) THEN INSERT INTO employee_audit VALUES (:new.empno, USER, 'Inserting a row into table_a'); ELSE INSERT INTO employee_audit VALUES (:new.empno, USER, 'Updating a row in table_a'); END IF; COMMIT; END;

    2. CREATE OR REPLACE TRIGGER format_table_trig AFTER INSERT ON format_table FOR EACH ROW WHEN (new.tablecode = 3334) DECLARE 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;

    3. CREATE OR REPLACE TRIGGER set_scott_on_logon AFTER logon ON SCHEMA DECLARE seq_sql VARCHAR(200) := 'alter package emp_pkg compile'; cursor_handle INTEGER := DBMS_SQL.open_cursor; execute_ddl INTEGER; BEGIN DBMS_SQL.parse (cursor_handle, seq_sql, DBMS_SQL.native); execute_ddl := DBMS_SQL.execute (cursor_handle); DBMS_SQL.close_cursor (cursor_handle); END;

  6. DDL and transaction control statements such as ROLLBACK, COMMIT, and SAVEPOINT are not allowed in the body of a trigger. Can you circumvent this restriction by calling a stored procedure, which does contain the statement, in the trigger's body?
  7. Sometimes, depending on the context of the application being constructed, you need to implement logic that requires explicit (or implicit) transaction control. For example, suppose you want to create a sequence when a user inserts a row into a table. How would you perform this function in Oracle8i? In previous versions of Oracle?
  8. True or false (note that number 32 really is a magical number for triggers!)?
    1. The trigger body cannot contain more than 32 lines of PL/SQL code.
    2. The size of a trigger cannot be more than 32K.
    3. Oracle allows up to 32 triggers to cascade at any one time.
    4. LONG or LONG RAW column can be referenced in a SQL statement within a trigger only if they can be converted into a constrained datatype. The maximum length for these datatypes can be up to 32K.

  9. Sometimes you need to enable or disable triggers when you perform certain tasks, such as loading data or reorganizing a table. Write a script that enables or disables all triggers for the user who runs it.

Expert

  1. If you create several triggers of the same type for the same table, in what order do the triggers fire?
  2. Optimize the performance of the following trigger and explain how this technique can minimize the number of times the trigger fires:
  3. CREATE OR REPLACE TRIGGER employee_ins_t1
       AFTER UPDATE OR DELETE OR INSERT
       ON employee
       FOR EACH ROW
    BEGIN
       IF (UPDATING) THEN
         IF :old.sal <> :new.sal THEN
           Employee_pkg.update_emp (:new.employee_id, :new.sal);
         END IF;
       END IF;
    END;
    
  4. An HR system has an employee table that holds a row for each employee within the company. Each record in the table has a manager field, (mgr), that holds the id for the employee's manager. Write a trigger so that when a manager record is deleted, the mgr field of that manager's employees is set to NULL. In other words, implement the following SQL statement:
  5. WHEN AN EMPLOYEE IS DELETED, 
       UPDATE employee SET 
          mgr = null
       WHERE 
          mgr = employee id of the deleted employee
    
  6. Due to a runtime error, an AFTER LOGON trigger in your database has become invalid. As a consequence, all users receive the following error when trying to connect to the database:
  7. ORA-04098: trigger 'ON_LOGON' is invalid and failed re-validation

    How can you fix the problem?

  8. What are the possible implications of using the pseudo-column names :OLD and :NEW in the following trigger, which uses autonomous transactions ?
  9. /* Filename on web page: trigauto.sql */

    CREATE OR REPLACE TRIGGER ins_emp
       AFTER INSERT
       ON emp
     FOR EACH ROW
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
       vsal   NUMBER;
    BEGIN
       SELECT SUM (sal)
         INTO vsal
         FROM emp e
        WHERE e.deptno = :new.deptno;
     
       BEGIN
          INSERT INTO dept_history
               VALUES (:new.deptno, vsal);
       EXCEPTION
          WHEN DUP_VAL_ON_INDEX
          THEN
             UPDATE dept_history
                SET sal = vsal
              WHERE deptno = :new.deptno;
       END;
     
       COMMIT;
    END;
    

  10. Suppose that the procedures called by the following triggers are recompiled. What happens at the next execution of each trigger?
  11. CREATE OR REPLACE TRIGGER loc_proc_trigger
       BEFORE UPDATE
       ON bonus
       FOR EACH ROW
    BEGIN
       update_bonus;
    END;
    /
     
    CREATE OR REPLACE TRIGGER rem_proc_trigger
       BEFORE UPDATE
       ON bonus
       FOR EACH ROW
    BEGIN
       update_bonus@rdb;
    END;
    /
    

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