Trigger

 

A Trigger in Oracle PL/SQL is a special type of stored procedure that automatically executes (fires) in response to specific database events like INSERT, UPDATE, DELETE, or DDL operations.

Why Use Triggers?

✅ Automate Business Rules – Enforce rules like maintaining an audit log.
✅ Data Integrity – Ensure consistent updates across multiple tables.
✅ Security – Restrict unauthorized changes.
✅ Performance Optimization – Perform actions without writing explicit code in applications.

Trigger Types :

Trigger Type	Fires On	Description
Row-Level Trigger	Each row affected	Executes once per affected row in a table.
Statement-Level Trigger	Each statement executed	Fires once per SQL statement, regardless of how many rows are affected.
Before Trigger	Before an event	Executes before an INSERT, UPDATE, or DELETE occurs.
After Trigger	After an event	Executes after an INSERT, UPDATE, or DELETE.
Instead of Trigger	On views	Replaces INSERT, UPDATE, or DELETE operations on a view.
DDL Trigger	On DDL changes	Fires when CREATE, ALTER, or DROP commands are executed.
Database Trigger	On system events	Fires on system-level events (LOGON, LOGOFF, STARTUP, etc.).

 

 

 

 

 

 

 

 

 

 

 

🔹 1. Row-Level Trigger

A Row-Level Trigger executes once for each affected row in a table.

🔹 Example: Audit Employee Salary Changes

CREATE OR REPLACE TRIGGER row_salary_audit

AFTER UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

    INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_on)

    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);

END;

/

🔹 Behavior:

If 5 rows are updated, the trigger runs 5 times (once per row).

Logs salary changes into an audit table.

 

🔹 2. Statement-Level Trigger

 Definition:

A Statement-Level Trigger executes once per SQL statement, regardless of the number of affected rows.

Example: Log Bulk Employee Updates

CREATE OR REPLACE TRIGGER statement_level_log

AFTER UPDATE ON employees

BEGIN

    INSERT INTO audit_log (event_type, event_time)

    VALUES ('Employee Update', SYSDATE);

END;

/

🔹 Behavior:

• If 100 employees' salaries are updated, the trigger fires once, logging the event.

 

🔹 3. BEFORE Trigger

•  Definition:
• A BEFORE Trigger executes before an INSERT, UPDATE, or DELETE statement occurs.
• Example: Automatically Set Employee Joining Date

CREATE OR REPLACE TRIGGER before_insert_employee

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

    -- Assign current timestamp before inserting a new employee

    :NEW.joining_date := NVL(:NEW.joining_date, SYSDATE);

END;

/

🔹 Behavior:

• Ensures that if joining_date is NULL, it is set to the current date before insertion.

 

🔹 4. AFTER Trigger

📌 Definition:

An AFTER Trigger executes after an INSERT, UPDATE, or DELETE operation.

Example: Maintain Employee Count in a Log Table

CREATE OR REPLACE TRIGGER after_insert_employee

AFTER INSERT ON employees

BEGIN

    INSERT INTO employee_log (event_type, event_time)

    VALUES ('New Employee Added', SYSDATE);

END;

/

🔹 Behavior:

After inserting a new employee, a log entry is recorded.

 

🔹 5. INSTEAD OF Trigger

📌 Definition:

An INSTEAD OF Trigger is used on views to replace the default behavior of INSERT, UPDATE, or DELETE.

🔹 Example: Allow Updates on a Read-Only View

CREATE OR REPLACE VIEW high_salary_employees AS

SELECT emp_id, emp_name, salary FROM employees WHERE salary > 50000;

 

CREATE OR REPLACE TRIGGER instead_of_update

INSTEAD OF UPDATE ON high_salary_employees

FOR EACH ROW

BEGIN

    UPDATE employees

    SET salary = :NEW.salary

    WHERE emp_id = :OLD.emp_id;

END;

/

🔹 Behavior:

·       Allows updates on a read-only view (high_salary_employees).

 

 

 

 

 

 

 

🔹 6. DDL Trigger

📌 Definition:

A DDL (Data Definition Language) Trigger fires when CREATE, ALTER, or DROP commands are executed.

🔹 Example: Log All Table Modifications

CREATE OR REPLACE TRIGGER ddl_trigger

AFTER CREATE OR DROP OR ALTER

ON SCHEMA

BEGIN

    INSERT INTO ddl_audit (event_type, event_time)

    VALUES (SYS_CONTEXT('USERENV', 'CURRENT_SQL'), SYSDATE);

END;

/

🔹 Behavior:

Logs every CREATE, ALTER, or DROP operation in the database.

🔹 7. Database Trigger

📌 Definition:

A Database-Level Trigger fires on system-wide events like LOGON, LOGOFF, STARTUP, or SHUTDOWN.

🔹 Example: Log User Logins

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON ON DATABASE

BEGIN

    INSERT INTO user_logins (username, login_time)

    VALUES (USER, SYSDATE);

END;

/

🔹 Behavior:Every time a user logs in, their username and login time are recorded.

 

Example 2 : INSTEAD OF Trigger on a Complex View

📌 Scenario:

You have two tables:

students (Stores student details)

courses (Stores course details)

You create a view student_courses_view that combines both tables. Normally, you cannot insert data into this view directly because it pulls data from multiple tables. However, using an INSTEAD OF trigger, we can allow inserting into the view by inserting data into the appropriate base tables.

---

Step 1: Create Base Tables

CREATE TABLE students (

    student_id NUMBER PRIMARY KEY,

    student_name VARCHAR2(100)

);

 

CREATE TABLE courses (

    student_id NUMBER REFERENCES students(student_id),

    course_name VARCHAR2(100)

);

---

Step 2: Create a View Combining Both Tables

CREATE OR REPLACE VIEW student_courses_view AS

SELECT s.student_id, s.student_name, c.course_name

FROM students s

LEFT JOIN courses c ON s.student_id = c.student_id;

🔹 Problem:

This view combines data from two tables (students and courses).

Inserting into this view normally would fail because the data needs to go into separate tables.

 

Step 3: Create an INSTEAD OF Trigger to Handle INSERTS

CREATE OR REPLACE TRIGGER instead_of_insert_student_course

INSTEAD OF INSERT ON student_courses_view

FOR EACH ROW

BEGIN

    -- Insert into students table

    INSERT INTO students (student_id, student_name)

    VALUES (:NEW.student_id, :NEW.student_name);

 

    -- Insert into courses table

    INSERT INTO courses (student_id, course_name)

    VALUES (:NEW.student_id, :NEW.course_name);

END;

/

Step 4: Insert Data into the View

INSERT INTO student_courses_view (student_id, student_name, course_name)

VALUES (1, 'John Doe', 'Mathematics');

🔹 What Happens?

Since views cannot store data, the INSTEAD OF trigger redirects the insert to the base tables.

The students table gets a new entry:

student_id | student_name 

-----------|------------- 

1          | John Doe 

 

The courses table gets a new entry:

 

student_id | course_name 

-----------|------------- 

1          | Mathematics