Oracle PL/SQL Tutorial/Trigger/Disable Enable Trigger

Alter trigger to disable it or enable it

   <source lang="sql">

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></source>

Disable a trigger

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

   <source lang="sql">

ALTER TRIGGER before_employee_salary_update DISABLE;</source>

Enable and disable triggers

   <source lang="sql">

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.</source>

Enable a trigger

   <source lang="sql">

ALTER TRIGGER before_employee_salary_update ENABLE;</source>