Oracle PL/SQL Tutorial/System Tables Data Dictionary/user triggers

30. Create alter trigger command by query user_triggers table

SQL>
SQL> SELECT   "ALTER TRIGGER " || trigger_name || " DISABLE;"
  2  FROM     user_triggers
  3  where    rownum < 50
  4  ORDER BY table_name;
"ALTERTRIGGER"||TRIGGER_NAME||"DISABLE;"
-----------------------------------------------------
ALTER TRIGGER APPLICATION_USERS_IOI DISABLE;
ALTER TRIGGER HRC_ORG_SITE_TRIG DISABLE;
ALTER TRIGGER STUDENT_TRIG DISABLE;
ALTER TRIGGER CAPTURE_TRACE_FILES DISABLE;
ALTER TRIGGER WHAT_COLUMN DISABLE;
SQL>
SQL>

30. find out all triggers from user_triggers table

SQL>
SQL>
SQL> SELECT    table_name, trigger_name, trigger_type type,
  2            triggering_event event, status
  3  FROM      user_triggers
  4  where     rownum < 2
  5  ORDER BY  table_name;
TABLE_NAME           TRIGGER_NAME                   TYPE
-------------------- ------------------------------ ----------------
EVENT
--------------------------------------------------------------------------------
STATUS
--------
HRC_ORG_SITE         HRC_ORG_SITE_TRIG              INSTEAD OF
INSERT OR UPDATE OR DELETE
DISABLED

SQL>
SQL>

30. Getting Information on Triggers

You can get information on your triggers from the user_triggers view.

SQL> desc user_triggers;
 Name                 Null?  Type
 ---------------------
 TRIGGER_NAME                VARCHAR2(30)
 TRIGGER_TYPE                VARCHAR2(16)
 TRIGGERING_EVENT            VARCHAR2(227) --Event that causes the trigger to fire.
 TABLE_OWNER                 VARCHAR2(30) --User who owns the table that the trigger references.
 BASE_OBJECT_TYPE            VARCHAR2(16) --Type of the object referenced by the trigger.
 TABLE_NAME                  VARCHAR2(30) --Name of the table referenced by the trigger.
 COLUMN_NAME                 VARCHAR2(4000) --Name of the column referenced by the trigger.
 REFERENCING_NAMES           VARCHAR2(128)  --Name of the old and new aliases.
 WHEN_CLAUSE                 VARCHAR2(4000) --Trigger condition that limits when the trigger runs its code.
 STATUS                      VARCHAR2(8)    --Whether the trigger is enabled or disabled. Set to ENABLED or DISABLED.
 DESCRIPTION                 VARCHAR2(4000) --Description of trigger.
 ACTION_TYPE                 VARCHAR2(11)   --Action type of the trigger. Set to CALL or PL/SQL.
 TRIGGER_BODY                LONG           --Code contained in the trigger body.