-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------Trigger in Oracle
---------------------------------------------------------
What is a Trigger?
A Trigger is a special kind of stored program in Oracle that automatically runs
when a specific event occurs in the database. Unlike stored procedures, triggers
execute on their own when a condition is met.
--------------------------------------------------------------
Why Use Triggers?
✅ Automatic Execution – Runs automatically when a database event happens (like
INSERT, UPDATE, DELETE).
✅ Data Integrity – Ensures that correct and consistent data is stored in the
database.
✅ Audit and Security – Keeps track of changes made to important tables.
✅ Prevention of Invalid Actions – Restricts unwanted modifications to data.
---------------------------------------------------------------------
Components of a Trigger
A trigger has three main parts:
1️
.Triggering Event – Defines when the trigger should fire (e.g., INSERT, UPDATE,
DELETE).
2️
.Triggering Condition – Defines the specific condition for execution.
3️
.Trigger Action – The code that runs when the event occurs.
------------------------------------------------------------------------
[Types of Triggers in Oracle]
->(1. Statement-Level Trigger)
-A statement-level trigger runs only once for a SQL statement, no matter how many
rows are affected.
-It does not check individual rows.
🔹 Example Scenario:
Imagine running an UPDATE command that modifies 100 rows in a table. A statement-
level trigger fires only once, not 100 times.
🔹 Example:
CREATE OR REPLACE TRIGGER statement_trigger_example
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Employees table updated!');
END;
->(2. Row-Level Trigger)
-A row-level trigger fires once for each row affected by the SQL statement.
-If 5 rows are updated, the trigger runs 5 times (once per row).
🔹 Example Scenario:
If an UPDATE statement changes 3 rows, a row-level trigger runs 3 times (once per
row).
🔹 Example:
CREATE OR REPLACE TRIGGER row_trigger_example
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Row updated: ' || :OLD.id);
END;
->(3. BEFORE Trigger)
-A BEFORE trigger runs before the actual SQL command is executed.
-It is useful for data validation (e.g., preventing negative salaries).
🔹 Example Scenario:
Before inserting a new employee, check if their salary is greater than zero.
🔹 Example:
CREATE OR REPLACE TRIGGER before_insert_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative!');
END IF;
END;
->(4. AFTER Trigger)
-An AFTER trigger runs after the SQL statement is executed.
-It is useful for logging, auditing, or tracking changes.
🔹 Example Scenario:
After deleting an employee, store their details in a backup table.
🔹 Example:
CREATE OR REPLACE TRIGGER after_delete_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_backup (id, name, salary)
VALUES (:OLD.id, :OLD.name, :OLD.salary);
END;
/
