Alter trigger to disable it or enable it : Disable Enable Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Disable Enable Trigger

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>