Trigger Options in Oracle

Trigger Options in Oracle with Example

In this article, I am going to discuss Trigger Options in Oracle with Examples. Please read our previous article where we discussed Trigger Firing Sequence in Oracle with Examples.

Trigger Options in Oracle with Example

In our previous article, we learnt about the Trigger Firing Sequence in Oracle. If a table is having four triggers the firing sequence of the triggers is as follows

  1. Before Statement
  2. Before Each Row
  3. After Each Row
  4. After Statement

We have a few more trigger actions that need to be learnt. Below are the few trigger options available in Oracle.

Compile Trigger Option in Oracle:

If the trigger is facing any issue i.e. not firing when required, we can compile the trigger again by executing the below SQL Statement.

alter trigger after_insert_each_row compile;

Compile Trigger Option in Oracle

Disable All Triggers Option in Oracle:

Disabling the trigger will help users not to fire the triggers when any event has occurred. Suppose, the table TEST_EMP has four triggers. The status of the triggers is set are enabled. Let us disable those triggers and check the status of the triggers again by executing the below SQL Statement.

Alter table test_emp disable all triggers;

Disable All Triggers Option in Oracle

So, the triggers are disabled. Let us check the status of the triggers in the table TEST_EMP

Disable All Triggers Option in Oracle

We can see all the triggers are disabled. We can even enable the triggers executing the following SQL statement.

alter table test_emp enable all triggers;

Trigger Options in Oracle with Examples

So, we have learnt how to enable all the triggers in Oracle. We can even disable and enable a single trigger by using the following statement.

alter trigger after_insert_stat disable;

Trigger Options in Oracle with Examples

So, the trigger is disabled. Let us try to enable the trigger by using the following statement.

alter trigger after_insert_stat enable;

Trigger Options in Oracle with Examples

Drop Trigger in Oracle:

We can even drop the trigger as well by executing the following statement

drop trigger after_insert_stat;

Drop Trigger in Oracle

Note: If we drop the table then triggers associated with the triggers are also dropped.

In the next article, I am going to discuss Populating Default Values using Triggers in Oracle with Examples. Here, in this article, I try to explain Trigger Options in Oracle with Examples. I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *