13.5 How do I…Disable and enable triggers?

Problem

When I perform batch processing and system management tasks on our database, I need to disable triggers from firing. Many of our triggers modify data in one or more tables. If I do not disable database triggers before performing these functions, they can fail. How do I disable and enable database triggers?

Technique

The ALTER TRIGGER statement is used to disable or enable a specific trigger. The DISABLE clause is used to disable a trigger, and the ENABLE clause is used to enable it. All triggers for a given table can be disabled or enabled together using the ALTER TABLE statement with the DISABLE ALL TRIGGERS or ENABLE ALL TRIGGERS clause.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP13_20.SQL, shown in Figure 13.20, creates a sample table and triggers used in this How-To. Run the statement to create the objects.

The script first drops the DEPT13 table along with the triggers created in the previous How-To’s. It then re-creates the DEPT13 table with two triggers that will be disabled and enabled in the examples. The BEFORE INSERT trigger—DEPT_BI_13—and an AFTER INSERT trigger—DEPT_AI_13—are created.

SQL> START CHP13-20

Table created.

Trigger created.

Trigger created.

2. Run the CHP13_21.SQL in SQL*Plus. The file contains an ALTER TRIGGER statement to disable a specific trigger. Figure 13.21 displays the execution of the DDL statement.

Line 1 presents the ALTER TRIGGER statement. The ALTER TRIGGER statement can be used to disable a specific trigger. Line 2 provides the name of the trigger to be disabled and line 3 specifies the DISABLE clause to disable the trigger.

When a record is inserted into the table, the BEFORE INSERT trigger will not fire because it was disabled by the ALTER TRIGGER statement. When you are done performing your management tasks, the trigger should be enabled. The next step provides the technique to enable a specific trigger.

3. Run the CHP13_22.SQL file in SQL*Plus. The file contains an ALTER TRIGGER statement to re-enable the trigger disabled in the previous step. Figure 13.22 displays the results of the operation.

The format of the statement is the same as presented in Step 2. The only difference is that line 3 specifies the ENABLE clause, which causes the trigger to be enabled. Once the trigger is enabled, it will fire whenever the triggering event occurs.

4. It is often necessary to disable all of the triggers on a given table. Run the CHP13_23.SQL file in SQL*Plus. The file contains an ALTER TABLE statement, which disables all of the triggers on the sample table. Figure 13.23 shows the output in SQL*Plus.

Line 1 specifies the ALTER TABLE statement and Line 2 specifies the table to be altered. Line 3 presents the DISABLE ALL TRIGGERS clause, disabling all triggers on the specified table. Using this technique does not require that you know the names of the triggers on the table.

Once you have completed your management tasks with the table, the triggers should be enabled. The next step presents the statement used to enable all the triggers on the table.

5. All the triggers on a table can be enabled using the ALTER TABLE statement. Run the CHP13_24.SQL file in SQL*Plus. This file contains the statement to re-enable all the triggers disabled in the previous step. Figure 13.24 shows the output after running the statement.

The first two lines of the ALTER TABLE statement are identical to the statement provided in Step 6. Line 3 of the statement presents the EN-ABLE ALL TRIGGERS clause, enabling all triggers on the specified table.

How It Works

The ALTER TABLE and ALTER TRIGGER statements disable and enable triggers. The ALTER TRIGGER statement is used to disable or enable a specific trigger by name. The ALTER TABLE statement with the ENABLE ALL TRIGGERS or DISABLE ALL TRIGGERS clause is used to enable or disable all triggers on the specified table. Step 1 creates a sample table and the two triggers used throughout this How-To. Step 2 presents the ALTER TRIGGER statement using the DISABLE clause to disable one of the sample triggers. Step 3 presents the ENABLE clause, to enable the sample trigger. Step 4 uses the ALTER TABLE statement to disable all the triggers on the sample table. Step 5 uses the ALTER TABLE statement with the ENABLE ALL TRIGGERS clause, enabling all the triggers on the sample table.

Comments

Database triggers ensure that a specific operation is performed when a table is modified. There will be times when you need to modify the data in a table but do not want the triggers to fire. The ALTER TABLE statement is the easiest way to disable and enable all the triggers on a table. It does not require that you remember the names of the triggers on a table.