What is a Trigger in PL/SQL

📌 What is a Trigger in PL/SQL?

A Trigger in PL/SQL is a database object that automatically executes when a specific event occurs on a table or view, such as INSERT, UPDATE, or DELETE.

Unlike procedures or functions, triggers fire implicitly—you don’t call them manually.

🔹 Why Triggers are Used

✔ Enforce business rules ✔ Automatically validate data ✔ Maintain audit logs (who changed what & when) ✔ Prevent invalid operations ✔ Maintain derived or dependent data

🔹 Types of Triggers

🔸 DML Triggers – INSERT, UPDATE, DELETE 🔸 BEFORE Trigger – Executes before the DML operation 🔸 AFTER Trigger – Executes after the DML operation 🔸 Row-Level Trigger – Executes for each row (FOR EACH ROW) 🔸 Statement-Level Trigger – Executes once per SQL statement 🔸 INSTEAD OF Trigger – Used with views

🔹 Simple Example

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

📝 This trigger automatically sets the created_date whenever a new employee record is inserted.

⚠️ Best Practices

❌ Avoid heavy business logic in triggers ❌ Don’t use triggers where procedures can handle logic clearly ✅ Keep triggers simple and efficient

🎯 Interview-Oriented Points

* Triggers are **event-driven**, not user-driven

* They execute **automatically**

* :NEWand:OLD are used in row-level triggers

* Cannot use COMMITorROLLBACK inside a trigger

* Triggers can impact performance if misused