Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / 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> drop trigger tr_log_ddl; Trigger dropped. SQL> SQL> drop table logit; Table dropped. SQL>