Oracle PL/SQL Programming, Third Edition

Maintaining Triggers

Oracle offers a number of DDL statements that can help you manage your triggers. You can enable, disable, and drop triggers, view information about triggers, and check the status of triggers, as explained in the following sections.

Disabling, Enabling, and Dropping Triggers

Disabling atrigger causes it not to fire when its triggering event occurs. Dropping a trigger causes it to be removed from the database altogether. The SQL syntax for disabling triggers is relatively simple compared to that for creating them:

ALTER TRIGGER trigger_name DISABLE;

For example:

ALTER TRIGGER emp_after_insert DISABLE;

A disabled trigger can also be re-enabled as shown in the following example:

ALTER TRIGGER emp_after_insert ENABLE;

The ALTER TRIGGER command is concerned only with the trigger name; it does not require identifying the trigger type or anything else. You can also easily create stored procedures to handle these steps for you. The following procedure, for example, uses dynamic SQL to disable or enable all triggers on a table:

/* File on web: settrig.sp */ CREATE OR REPLACE PROCEDURE settrig (tab IN VARCHAR2, action IN VARCHAR2) IS v_action VARCHAR2 (10) := UPPER (action); v_other_action VARCHAR2 (10) := 'DISABLED'; BEGIN IF v_action = 'DISABLE' THEN v_other_action := 'ENABLED'; END IF; FOR rec IN (SELECT trigger_name FROM user_triggers WHERE table_owner = USER AND table_name = UPPER (tab) AND status = v_other_action) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.trigger_name ...