Oracle7 Server Application Developer's Guide

Listing Information About Triggers

The following data dictionary views reveal information about triggers:

USER_TRIGGERS

ALL_TRIGGERS

DBA_TRIGGERS

The Oracle7 Server Reference manual gives a complete description of these data dictionary views. For example, assume the following statement was used to create the REORDER trigger:

CREATE TRIGGER reorder
AFTER UPDATE OF parts_on_hand ON inventory
FOR EACH ROW
WHEN(new.parts_on_hand < new.reorder_point)
DECLARE
   x NUMBER;
BEGIN
   SELECT COUNT(*) INTO x
      FROM pending_orders
      WHERE part_no = :new.part_no;
   IF x = 0  THEN
      INSERT INTO pending_orders
         VALUES (:new.part_no, :new.reorder_quantity,
                 sysdate);
   END IF;
END;

The following two queries return information about the REORDER trigger:

SELECT trigger_type, triggering_event, table_name
   FROM user_triggers
   WHERE name = 'REORDER';

TYPE             TRIGGERING_STATEMENT       TABLE_NAME
---------------- -------------------------- ------------
AFTER EACH ROW   UPDATE                     INVENTORY

SELECT trigger_body
   FROM user_triggers
   WHERE name = 'REORDER';

TRIGGER_BODY
--------------------------------------------
DECLARE
   x NUMBER;
BEGIN
   SELECT COUNT(*) INTO x
      FROM pending_orders
      WHERE part_no = :new.part_no;
   IF x = 0
      THEN INSERT INTO pending_orders
         VALUES (:new.part_no, :new.reorder_quantity,
            sysdate);
   END IF;
END;