TRIGGERS in Oracle PLSQL

Oracle PLSQL TRIGGER is a set of statements that get executed or triggered as a side effect of modification of database.

Oracle PLSQL Triggers can be categorized as:

1. Insert Trigger
2. Update Trigger
3. Delete Trigger

1. Insert Trigger:- This type of plsql trigger gets executed as soon as an INSERT action is performed on a table.

Insert trigger can be of two types:

• Before Insert Trigger
• After Insert Trigger

2. Update Trigger:- This type of plsql trigger gets executed as soon as an UPDATE action is performed on a table.

Update Trigger can be of 2 types:

• Before Update Trigger
• After Update Trigger

3. Delete Trigger:- This type of plsql trigger gets executed as soon as a DELETE action is performed on a table.

Delete Trigger can be of 2 types:

• Before Delete Trigger
• After Delete Trigger

Please click on the trigger types above to understand the detailed explanation of each trigger type with examples.

---

Drop an Oracle PLSQL trigger:

Oracle PLSQL allows up to drop a trigger which we have created previously

PLSQL Syntax to drop a trigger is:

DROP TRIGGER trigger_name;

Example to drop a PLSQL trigger:

DROP TRIGGER trigger_employee_update;

The above drop statement will drop the trigger named ‘trigger_employee_update’ from the data base.

---

Enable and Disable an Oracle PLSQL Trigger:

1. Enable a PLSQL Trigger

Oracle PLSQL allows us to enable the trigger that we have created and disabled previously using ALTER statement and ENABLE keyword.

Oracle PLSQL syntax to enable a trigger is:

ALTER TRIGGER trigger_name;

Example to enable a PLSQL trigger:

ALTER TRIGGER trigger_employee_update ENABLE;

The above drop statement will enable the trigger named ‘trigger_employee_update’.

2. Enable all PLSQL Triggers on a table

Oracle PLSQL allows us to enable all the triggers on a table at once using the ALTER statement with the ENABLE ALL TRIGGERS keyword.

Syntax to enable all PLSQL triggers on a table is:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

Example to enable all Oracle PLSQL triggers on a table:

ALTER TABLE employee ENABLE ALL TRIGGERS;

The above ALTER TABLE statement will enable all the triggers on ‘employee’ table.

3.Disable an Oracle PLSQL Trigger

We can disable a PLSQL Trigger that we have created earlier by using the ALTER statement with the DISABLE keyword.

Oracle PLSQL syntax to disable a trigger is:

ALTER TRIGGER trigger_name DISABLE;

Example to disable a PLSQL trigger in PLSQL:

ALTER TRIGGER trigger_employee_update DISABLE;

The above ALTER TRIGGER statement will disable the ‘trigger_employee_update’ trigger.

4. Disable all Oracle PLSQL Triggers on a table

Oracle PLSQL allows us to disable all the trigger on a table at once using the ALTER TABLE Statement with the DISABLE ALL TRIGGERS keyword.

Syntax to disable all plsql triggers on a table is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Example to disable all PLSQL triggers on a table:

ALTER TABLE employee DISABLE ALL TRIGGERS;

The above statement will disable all the triggers on ‘employee’ table.

---