Oracle PL/SQL Programming, 5th 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 a trigger 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 reenabled 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 */ PROCEDURE settrig ( tab IN VARCHAR2 , sch IN VARCHAR DEFAULT NULL , action IN VARCHAR2 ) IS l_action VARCHAR2 (10) := UPPER (action); l_other_action VARCHAR2 (10) := 'DISABLED'; BEGIN IF l_action = 'DISABLE' THEN l_other_action := 'ENABLED'; END IF; FOR rec IN (SELECT trigger_name FROM user_triggers WHERE table_owner = UPPER (NVL (sch, USER)) AND table_name = tab AND status = l_other_action) LOOP EXECUTE IMMEDIATE 'ALTER TRIGGER ...