O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required