Lesson 29 Advanced Level
29 of 30 lessons

Triggers

Learn about database triggers, their types, and how to create them for automated actions. Triggers enable automatic database operations.

What is a Trigger?

A trigger is a stored PL/SQL block that is automatically executed (or "fired") in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE.

Types of Triggers

  • BEFORE Trigger – Executes before the triggering event.
  • AFTER Trigger – Executes after the event.
  • INSTEAD OF Trigger – Used for views to override default actions.
  • ROW-level Trigger – Fires once for each row affected.
  • STATEMENT-level Trigger – Fires once for the entire SQL statement.

Basic Trigger Example

CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_at := SYSDATE;
END;
/

This trigger sets the created_at column to the current date before inserting a new row into the employees table.

Viewing Triggers

SELECT trigger_name, table_name, status
FROM user_triggers;

Dropping a Trigger

DROP TRIGGER trg_before_insert;

Use Cases for Triggers

  • Audit changes to sensitive data
  • Automatically enforce business rules
  • Prevent invalid transactions
  • Log user actions

Practice Task