Back to: Oracle Tutorials for Beginners and Professionals
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
- Before Statement
- Before Each Row
- After Each Row
- 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;
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;
So, the triggers are disabled. Let us check the status of the triggers in the table TEST_EMP
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;
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;
So, the trigger is disabled. Let us try to enable the trigger by using the following statement.
alter trigger after_insert_stat enable;
Drop Trigger in Oracle:
We can even drop the trigger as well by executing the following statement
drop trigger after_insert_stat;
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.