PL/SQL Row Level Triggers – Oracle PL/SQL Tutorial

Oracle PL/SQL provides a powerful and flexible mechanism for database developers to implement business logic within the database itself. One essential feature in PL/SQL is the use of triggers, which are blocks of code that automatically execute in response to specific events on a particular table or view. Among the various types of triggers, row-level triggers are significant as they operate on each affected row during the execution of SQL statements.

Row-level triggers are invoked for each row affected by the triggering event, and they allow developers to enforce complex business rules, maintain data integrity, and perform other actions at the individual row level. There are two main types of row-level triggers in Oracle PL/SQL: BEFORE ROW and AFTER ROW triggers.

BEFORE ROW Triggers:

A BEFORE ROW trigger is fired before any data modifications occur due to the triggering statement (INSERT, UPDATE, or DELETE).
These triggers are often used to enforce data validation rules or perform data modifications before the changes are committed to the database.
For example, a BEFORE INSERT trigger can validate and modify data before it is inserted into the table.

CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
   -- Your PL/SQL code here
END;

AFTER ROW Triggers:

An AFTER ROW trigger is executed after the data modifications have been made to the table.
These triggers are commonly used for tasks such as auditing, logging changes, or updating related tables after the primary table is modified.
For instance, an AFTER UPDATE trigger can log the changes made to a table.

CREATE OR REPLACE TRIGGER after_update_trigger
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
   -- Your PL/SQL code here
END;

In both cases, the FOR EACH ROW clause specifies that the trigger should be executed once for each row affected by the triggering event. The “:OLD” and “:NEW” keywords can be used within the trigger to reference the old and new values of the columns being modified, providing a way to compare values and make decisions based on the changes.

CREATE OR REPLACE TRIGGER example_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
   IF :OLD.column_name != :NEW.column_name THEN
      -- Your logic for handling the change
   END IF;
END;

Row-level triggers in Oracle PL/SQL play a crucial role in maintaining data consistency, enforcing business rules, and implementing custom logic within the database environment. However, it’s important to use them judiciously, considering performance implications and potential complexities that may arise with extensive trigger usage.

Learn PL/SQL