Triggers

A trigger is a stored PL/SQL program that automatically executes (or "fires") when a specified database event occurs. Triggers are often used to enforce business rules, maintain audit trails, or perform automated actions when data is modified.

2. Types of Triggers

Triggers can be classified based on their level and timing:

2.1 Row-Level vs Statement-Level Triggers

• Row-Level Triggers:

  • Execute once for each row affected by the triggering event.
  • Commonly used for data validations and audits.

  Example: For every row inserted into the employees table, calculate and store an additional column value.

• Statement-Level Triggers:

  • Execute once for the entire SQL statement, regardless of the number of rows affected.
  • Used for operations like logging or enforcing table-level constraints.

  Example: Log the number of rows affected by an update on the employees table.

2.2 BEFORE and AFTER Triggers

• BEFORE Triggers:

  • Execute before the triggering SQL operation.
  • Used to validate or modify the data before it is committed to the database.

  Example: Validate that a salary value is within a specific range before an INSERT.

• AFTER Triggers:

  • Execute after the triggering SQL operation has completed.
  • Used for actions that depend on the success of the SQL operation.

  Example: Write a log entry after a record is successfully updated.

2.3 INSTEAD OF Triggers

• Used for views that cannot be directly modified.
• Allow you to define actions to be performed on the underlying tables when a DML operation is issued on the view.

3. Trigger Syntax

plsql
Copy code
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} triggering_event
ON table_or_view_name
[FOR EACH ROW]
DECLARE
   -- Declarations (optional)
BEGIN
   -- Logic to execute
END;
/

4. Examples of Triggers

4.1 Audit Trail Using Triggers

An audit trail records changes to a table in a separate log table.

Scenario:

Track INSERT, UPDATE, and DELETE operations on the employees table in the audit_employees table.

Step 1: Create the Audit Table

plsql
Copy code
CREATE TABLE audit_employees (
   audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   action_type VARCHAR2(10),
   employee_id NUMBER,
   change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   changed_by VARCHAR2(50)
);

Step 2: Create the Trigger

plsql
Copy code
CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
   IF INSERTING THEN
      INSERT INTO audit_employees (action_type, employee_id, changed_by)
      VALUES ('INSERT', :NEW.employee_id, USER);
   ELSIF UPDATING THEN
      INSERT INTO audit_employees (action_type, employee_id, changed_by)
      VALUES ('UPDATE', :OLD.employee_id, USER);
   ELSIF DELETING THEN
      INSERT INTO audit_employees (action_type, employee_id, changed_by)
      VALUES ('DELETE', :OLD.employee_id, USER);
   END IF;
END;
/

Explanation:

• :NEW refers to the new values being inserted or updated.
• :OLD refers to the existing values being deleted or updated.

4.2 Restricting Certain Operations

Scenario:

Prevent employees from being deleted if their salary is above 100,000.

Trigger Code:

plsql
Copy code
CREATE OR REPLACE TRIGGER trg_restrict_high_salary_delete
BEFORE DELETE
ON employees
FOR EACH ROW
BEGIN
   IF :OLD.salary > 100000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employees with a salary above 100,000.');
   END IF;
END;
/

Explanation:

• The trigger fires before a DELETE operation.
• The RAISE_APPLICATION_ERROR function generates a custom error message if the condition is met.

4.3 INSTEAD OF Trigger Example

Scenario:

Allow updates to a view by redirecting the operation to the underlying table.

Step 1: Create a View

plsql
Copy code
CREATE VIEW emp_view AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

Step 2: Create the Trigger

plsql
Copy code
CREATE OR REPLACE TRIGGER trg_instead_of_update
INSTEAD OF UPDATE
ON emp_view
FOR EACH ROW
BEGIN
   UPDATE employees
   SET salary = :NEW.salary
   WHERE employee_id = :OLD.employee_id;
END;
/

Explanation:

• The INSTEAD OF trigger intercepts the UPDATE operation on the view and performs it on the underlying table.

5. Advantages of Using Triggers

1. Enforce Business Rules:

   • Ensure data integrity and consistency (e.g., preventing invalid data modifications).

2. Automate Actions:

   • Perform tasks like auditing, logging, or maintaining derived data automatically.

3. Simplify Application Logic:

   • Reduce the need to include validation or audit logic in every application accessing the database.

4. Centralized Control:

   • Apply rules and constraints at the database level, ensuring consistency across applications.

Summary of Trigger Features

Final Notes

• Triggers are a powerful mechanism to enforce rules and automate tasks, but overuse can lead to performance issues and complexity.
• Always carefully design triggers to ensure they don't introduce unintended side effects or recursive behavior.