Concept of Triggers

 

In PL/SQL, a trigger is a stored procedural code block that executes automatically (“fires”) in response to specific events on a table, view, schema, or the database itself.

 

Classification of Triggers
Triggers are categorized based on their timing, the level at which they operate, and the types of events that activate them.
 
1. Based on Execution Timing
  • BEFORE Trigger: Fires before the triggering DML statement is executed. It is ideal for validating or modifying data before it is saved.
  • AFTER Trigger: Fires after the triggering statement has completed. 
  • INSTEAD OF Trigger: Used exclusively on views to perform custom DML operations instead of the standard database actions.

 

2. Based on Execution Level
  • Row-Level Trigger: Identified by the FOR EACH ROW clause, this trigger fires once for every individual row affected by the SQL statement. If an UPDATE affects 100 rows, a row-level trigger fires 100 times.
  • Statement-Level Trigger: The default type if FOR EACH ROW is omitted. It fires only once per SQL statement, regardless of how many rows are affected (even if zero rows are impacted).

 

Advantages of Triggers
  • Automated Auditing
  • Enforcing Complex Security
  • Data Integrity
  • Synchronization

 

Disadvantages of Triggers
  • Performance Overhead
  • “Invisible” Logic
  • Complex Troubleshooting
  • Maintenance Hurdles
    PreviousNext