Row-level triggers (FOR EACH ROW) And Statement-level triggers

In Oracle PL/SQL, triggers can be either row-level triggers (FOR EACH ROW) or statement-level triggers. Understanding the difference between them is crucial for designing how database actions should be performed. Let's break down each type with examples and sample data.

1. Row-Level Trigger (FOR EACH ROW)

Definition: A row-level trigger is fired once for each row affected by the triggering event (e.g., INSERT, UPDATE, DELETE).

Use case: You want to perform an action for each individual row affected by a DML statement.

Example of Row-Level Trigger

Suppose you have an employees table, and you want to automatically set a default value for a column or modify specific data for each row before it's inserted.

Table: employees

CREATE TABLE employees (
employee_id NUMBER,
employee_name VARCHAR2(50),
salary NUMBER,
department VARCHAR2(50)
);

Row-Level Trigger: Automatically Adjust Salary Before Inserting

The following trigger will automatically increase the salary by 10% for each row before it is inserted.

CREATE OR REPLACE TRIGGER adjust_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.salary := :NEW.salary * 1.10; -- Increase salary by 10% for each new row
END;
/

Explanation:

FOR EACH ROW: The trigger will fire once for each row being inserted into the employees table.

:NEW.salary: This refers to the value of the salary column in the new row being inserted. It adjusts the salary by 10% before the row is inserted.

Inserting Data:

INSERT INTO employees (employee_id, employee_name, salary, department)
VALUES (1, 'John Doe', 5000, 'HR');

The row-level trigger will automatically adjust the salary, so instead of inserting 5000, the inserted salary will be 5500 (5000 * 1.10).

1. Statement-Level Trigger

Definition: A statement-level trigger is fired once for the entire triggering event, regardless of how many rows are affected by that event.

Use case: You want to perform an action only once, even if multiple rows are affected by a single DML statement.

Example of Statement-Level Trigger

Let's say you want to log when any insert operation is performed on the employees table, but you don't care how many rows are inserted.

Statement-Level Trigger: Log Insert Operations

This trigger logs a message each time an INSERT statement is executed on the employees table, regardless of how many rows are affected.

CREATE OR REPLACE TRIGGER log_insert_on_employees
AFTER INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert operation on employees table');
END;
/

Explanation:

No FOR EACH ROW: Since there is no FOR EACH ROW clause, this is a statement-level trigger. It fires once after the INSERT statement is executed, no matter how many rows are inserted.

DBMS_OUTPUT: Outputs a message saying an insert operation occurred.

Inserting Multiple Rows:

INSERT INTO employees (employee_id, employee_name, salary, department)
VALUES (2, 'Jane Smith', 6000, 'Finance');

INSERT INTO employees (employee_id, employee_name, salary, department)
VALUES (3, 'Tom Brown', 7000, 'Engineering');

The trigger will output "Insert operation on employees table" once, even if multiple rows are inserted, because it is a statement-level trigger.

---

Key Differences:

---

Practical Example with Data

1. Row-Level Trigger Example

Let's say we want to log every employee's ID and name being updated.

CREATE OR REPLACE TRIGGER log_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Updated Employee: ' || :OLD.employee_id || ' - ' || :OLD.employee_name);
END;
/

Explanation:

This trigger logs every individual employee whose details are updated.

Update Data:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'HR';

If 5 rows are updated, this trigger will fire five times, once for each employee updated, logging each employee's ID and name.

---

1. Statement-Level Trigger Example

Now let's say we just want to log that an update occurred, without caring how many employees were updated.

CREATE OR REPLACE TRIGGER log_update_on_employees
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Update operation on employees table');
END;
/

Explanation:

This trigger logs a single message after any update occurs, regardless of how many rows are affected.

Update Data:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'HR';

Even if 5 rows are updated, this trigger fires once and logs a single message.

---

When to Use Row-Level vs Statement-Level Triggers

Use a row-level trigger (FOR EACH ROW) when you need to process each row individually, such as modifying or validating data on a per-row basis.

Use a statement-level trigger when you need to perform a task once per DML statement, such as logging, auditing, or updating a global counter.

Both types of triggers can be useful in different scenarios depending on whether you need to handle each row specifically or handle the entire operation as a whole.

.
.
.
.

More explanation

Here are additional examples of row-level and statement-level triggers in various use cases. These examples will help further illustrate the differences and applications of each trigger type in Oracle PL/SQL.

---

Row-Level Trigger Examples

1. Automatic Audit Trail for Data Changes

Let's say we want to maintain an audit trail for updates on the employees table, where we log the changes made to employee salaries.

Table: salary_audit

We will create a table to store the audit logs.

CREATE TABLE salary_audit (
employee_id NUMBER,
old_salary NUMBER,
new_salary NUMBER,
change_date DATE
);

Row-Level Trigger: Log Salary Changes

This row-level trigger will fire every time an employee's salary is updated, storing the old and new salary in the salary_audit table.

CREATE OR REPLACE TRIGGER audit_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
-- Insert old and new salary values into the audit table
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

Explanation:

AFTER UPDATE OF salary: The trigger fires after an update is made to the salary column.

:OLD.salary and :NEW.salary: Access the old and new values of the salary for each row.

FOR EACH ROW: The trigger fires for every individual row where the salary is updated, ensuring an audit record is inserted for each row.

Test the Trigger:

UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = 101;

The above update will trigger the insertion of the old and new salary values into the salary_audit table for employee 101.

---

1. Enforcing Business Rules on Inserts

Suppose there's a business rule that no employee can be inserted into the employees table with a salary below 3000. We will use a row-level trigger to enforce this rule.

Row-Level Trigger: Enforce Minimum Salary

CREATE OR REPLACE TRIGGER check_minimum_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 3000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be less than 3000');
END IF;
END;
/

Explanation:

BEFORE INSERT OR UPDATE: The trigger fires before an INSERT or UPDATE to check the new salary value.

:NEW.salary: Refers to the salary being inserted or updated for each row.

RAISE_APPLICATION_ERROR: Raises a custom error if the salary is less than 3000, preventing the insert or update from proceeding.

Test the Trigger:

INSERT INTO employees (employee_id, employee_name, salary, department)
VALUES (102, 'Alice Johnson', 2500, 'Sales');

The trigger will raise an error: "Salary cannot be less than 3000" and the insert will be prevented.

---

Statement-Level Trigger Examples

1. Prevent Bulk Deletion of Critical Data

Suppose we want to prevent the deletion of more than 5 rows at a time from the employees table. We can use a statement-level trigger to check how many rows are being deleted and raise an error if the count exceeds 5.

Statement-Level Trigger: Prevent Mass Deletion

CREATE OR REPLACE TRIGGER prevent_mass_delete
BEFORE DELETE ON employees
DECLARE
v_rowcount NUMBER;
BEGIN
-- Count the number of rows that will be deleted
SELECT COUNT(*) INTO v_rowcount FROM employees
WHERE employee_id IN (SELECT employee_id FROM employees WHERE employee_id = :OLD.employee_id);

-- Raise an error if more than 5 rows are affected
IF v_rowcount > 5 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Cannot delete more than 5 employees at a time');
END IF;

END;
/

Explanation:

BEFORE DELETE: The trigger fires before any delete statement is executed on the employees table.

v_rowcount: This variable holds the number of rows that would be deleted by the statement.

RAISE_APPLICATION_ERROR: If more than 5 rows are being deleted, an error is raised, and the deletion is prevented.

Test the Trigger:

DELETE FROM employees
WHERE department = 'Sales';

If more than 5 employees work in the Sales department, the trigger will raise an error, preventing the deletion.

---

1. Log All Insert Operations

You may want to log every time an insert operation occurs on a table, regardless of how many rows are inserted. A statement-level trigger can handle this.

Statement-Level Trigger: Log Insert Actions

CREATE OR REPLACE TRIGGER log_insert_action
AFTER INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert operation performed on employees table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END;
/

Explanation:

AFTER INSERT: The trigger fires after an insert statement is executed on the employees table.

DBMS_OUTPUT.PUT_LINE: Outputs a message indicating that an insert operation was performed, along with the current timestamp.

Test the Trigger:

INSERT INTO employees (employee_id, employee_name, salary, department)
VALUES (104, 'Bob Adams', 4500, 'IT');

The trigger will output a message like "Insert operation performed on employees table at 01-OCT-2024 15:20:45", regardless of how many rows were inserted.

---

1. Automatically Log Updates with a Statement-Level Trigger

This example logs the details of any UPDATE operation made on the employees table. Instead of logging row-specific information, we log a message when any UPDATE statement occurs.

Statement-Level Trigger: Log Update Operations

CREATE OR REPLACE TRIGGER log_update_action
AFTER UPDATE ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Update operation performed on employees table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END;
/

Explanation:

AFTER UPDATE: The trigger fires after any UPDATE statement is executed on the employees table.

DBMS_OUTPUT.PUT_LINE: Outputs a log message that an update occurred, along with the current timestamp.

Test the Trigger:

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'HR';

The trigger will output a message like "Update operation performed on employees table at 01-OCT-2024 16:45:30", regardless of how many rows were updated.

---

Summary of Row-Level vs. Statement-Level Triggers

Row-Level Triggers:

Execute once per row affected by the DML operation.

Use when specific actions are needed for each row (e.g., auditing, enforcing business rules).

Access row-specific information via :NEW and :OLD pseudorecords.

Statement-Level Triggers:

Execute once per DML statement, regardless of the number of rows affected.

Use for global operations like logging, preventing mass actions, or enforcing constraints that affect the whole table.

By choosing the appropriate trigger type based on the requirement, you can ensure efficient and effective database operations.