Oracle PL/SQL Tutorial/Trigger/Disable Enable Trigger

Alter trigger to disable it or enable it

SQL>
SQL> CREATE TABLE logit
  2  ( event VARCHAR2(400) );
Table created.
SQL>
SQL> create or replace trigger tr_log_ddl
  2  before create or drop on SCHEMA
  3  begin
  4     insert into logit values ("Object Name: "|| ora_dict_obj_name
  5                             || " Type: "  || ora_dict_obj_type
  6                             || " Event: " || ora_sysevent
  7                             || " " || sysdate) ;
  8  end ;
  9  /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> create table dummy (col1  varchar2(10) ) ;
Table created.
SQL>
SQL> drop table dummy ;
Table dropped.
SQL>
SQL> Select * from LOGIT;
EVENT
--------------------------------------------------------------------------------
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08
SQL>
SQL> alter trigger tr_log_ddl disable ;
Trigger altered.
SQL>
SQL> create table dummy (col1 varchar2(10) ) ;
Table created.
SQL>
SQL> select * from logit ;
EVENT
--------------------------------------------------------------------------------
Object Name: DUMMY Type: TABLE Event: CREATE 24-JUL-08
Object Name: DUMMY Type: TABLE Event: DROP 24-JUL-08
SQL>
SQL> select object_name, status from user_objects where object_type = "TRIGGER" ;
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
HRC_ORG_SITE_TRIG
INVALID
CAPTURE_TRACE_FILES
INVALID
WHAT_COLUMN
VALID

OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
LOGOFF_LOG_TRIGGER
INVALID
APPLICATION_USERS_IOI
INVALID
STUDENT_TRIG
VALID

OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
TR_LOG_DDL
VALID

7 rows selected.
SQL>
SQL> alter trigger tr_log_ddl enable ;
Trigger altered.
SQL>
SQL>
SQL> drop trigger tr_log_ddl;
Trigger dropped.
SQL>
SQL> drop table logit;
Table dropped.
SQL>
SQL>
SQL>
SQL>

Disable a trigger

You can stop a trigger from firing by disabling it using the ALTER TRIGGER statement.

ALTER TRIGGER before_employee_salary_update DISABLE;

Enable and disable triggers

SQL>
SQL>
SQL> SET VERIFY OFF;
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     cur INTEGER;
  3     done EXCEPTION;
  4     cnt NUMBER := 0;
  5  BEGIN
  6     FOR user_trg IN (SELECT trigger_name FROM user_triggers)
  7     LOOP
  8        BEGIN
  9           cnt := cnt + 1;
 10           cur := DBMS_SQL.open_cursor;
 11
 12           DBMS_SQL.parse (cur,"ALTER TRIGGER  " || user_trg.trigger_name || " ENABLE",DBMS_SQL.native);
 13           DBMS_SQL.parse (cur,"ALTER TRIGGER " || user_trg.trigger_name || " DISABLE",DBMS_SQL.native);
 14        EXCEPTION
 15           WHEN OTHERS
 16           THEN
 17              DBMS_OUTPUT.put_line (SQLCODE || "-" || SQLERRM);
 18              DBMS_SQL.close_cursor (cur);
 19        END;
 20     END LOOP;
 21  END;
 22  /
PL/SQL procedure successfully completed.

Enable a trigger

ALTER TRIGGER before_employee_salary_update ENABLE;