Use NDS to disable the trigger : Disable Trigger « Trigger « Oracle PL / SQL

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

Use NDS to disable the trigger

    

SQL>
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>
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> DECLARE
  2     v_statement VARCHAR2(500);
  3     CURSOR trigger_cur IS SELECT trigger_name FROM user_triggers;
  4  BEGIN
  5     FOR y IN trigger_cur
  6     LOOP
  7        v_statement := 'ALTER TRIGGER '||y.trigger_name||' DISABLE';
  8
  9        EXECUTE IMMEDIATE v_statement;
 10     END LOOP;
 11
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> SELECT trigger_name, status
  2  FROM user_triggers;

TRIGGER_NAME                   STATUS
------------------------------ --------
EMP_TRIG                       DISABLED
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> SET ESCAPE OFF
SQL> drop table emp;

Table dropped.

   
    
    
    
  

Related examples in the same category