Oracle PL/SQL/System Tables Views/user triggers

Check the status of the trigger

   
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "E", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER emp_trig
  2     BEFORE UPDATE OF fname
  3     ON emp
  4     FOR EACH ROW
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE("First Name "||:OLD.fname||" has change to "||:NEW.fname);
  7  END;
  8  /
Trigger created.
SQL>
SQL>
SQL> SELECT trigger_name, status FROM user_triggers;
TRIGGER_NAME                   STATUS
------------------------------ --------
EMP_TRIG                       ENABLED
INVENTORY_TRG                  DISABLED
DEMO_BIFER                     DISABLED
DEPT_SAL_TRG                   DISABLED
SYSTEMALTERUSER                DISABLED
USAGE_START                    DISABLED
USAGE_STOP                     DISABLED
USER_LOGON_MODULE_CHECK        DISABLED
8 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

If trigger exists, drop trigger

   
SQL>
SQL> BEGIN
  2    FOR i IN (SELECT null FROM user_triggers WHERE trigger_name = "INSERTemp") LOOP
  3      EXECUTE IMMEDIATE "DROP TRIGGER InsertBooksemp";
  4    END LOOP;
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>

Query user_triggers

   
SQL>
SQL>
SQL> select trigger_name, trigger_type,
  2       triggering_event, trigger_body
  3  from user_triggers
  4  where rownum < 50;
TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
AW_DROP_TRG                    AFTER EVENT
DROP
BEGIN
  aw_drop_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);

SQL>

Query user_triggers with trigger name

   
SQL>
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );
Table created.
SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created.
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created.
SQL>
SQL>
SQL>
SQL> create or replace trigger MyTrigger
  2    before insert or update
  3      of department_id
  4      on employees
  5    referencing old as old_value
  6                new as new_value
  7    for each row
  8      when ( new_value.department_id <> 80 )
  9  begin
 10    :new_value.rumission_pct := 0;
 11  end;
 12  /
Warning: Trigger created with compilation errors.
SQL>
SQL>
SQL> select trigger_type, triggering_event, when_clause, trigger_body
  2    from user_triggers
  3    where trigger_name = "MYTRIGGER";
TRIGGER_TYPE     TRIGGERING_EVENT
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------
WHEN_CLAUSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
BEFORE EACH ROW  INSERT OR UPDATE
 new_value.department_id <> 80
begin
  :new_value.rumission_pct := 0;
end;

SQL>
SQL> drop table employees;
Table dropped.
SQL>

Restore trigger create statement from user_trigger table

   
SQL>
SQL>
SQL>
SQL>
SQL> select
  2  "create or replace trigger "" || trigger_name || """ || chr(10)||
  3   decode( substr( trigger_type, 1, 1 ),"A", "AFTER", "B", "BEFORE", "I", "INSTEAD OF" ) ||chr(10) ||triggering_event || chr(10) ||
  4   "ON "" || table_owner || ""."" || table_name || """ || chr(10) ||
  5   decode( instr( trigger_type, "EACH ROW" ), 0, null,"FOR EACH ROW" ) || chr(10) ,
  6   trigger_body
  7  from user_triggers
  8  where trigger_name = upper("YourTriggerName")
  9        and rownum < 50
 10  /
no rows selected
SQL>
SQL> --