• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / plsql / TRIGGERS in Oracle PLSQL

TRIGGERS in Oracle PLSQL

December 11, 2012 by techhoneyadmin

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.


Filed Under: plsql Tagged With: CREATETRIGGERSPLSQL, drop enable and disable trigger in oracle plsql, How to create triggers in oracle plsql, types of triggers in oracle plsql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in