Triggers in Oracle

Triggers in Oracle are database objects that are automatically executed or fired in response to certain events occurring in the database. These events could include DML (Data Manipulation Language) operations such as INSERT, UPDATE, DELETE, or DDL (Data Definition Language) operations like CREATE, ALTER, DROP. Triggers are commonly used to enforce business rules, maintain data integrity, and automate tasks.

Here's an example of a trigger in Oracle along with a real-life scenario:

Example:

Suppose we have a table named employees with the following structure:

CREATE TABLE employees (

    employee_id NUMBER PRIMARY KEY,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50),

    salary NUMBER

);

Now, let's say we want to create a trigger that automatically updates the salary column of an employee whenever their salary is modified. The trigger will log the old and new salary values into another table called salary_audit.

Here's how we can define such a trigger:

CREATE TABLE salary_audit (

    employee_id NUMBER,

    old_salary NUMBER,

    new_salary NUMBER,

    audit_date DATE

);

CREATE OR REPLACE TRIGGER salary_update_trigger

BEFORE UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

    INSERT INTO salary_audit (employee_id, old_salary, new_salary, audit_date)

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

END;

/

In this trigger:

- salary_update_trigger is the name of the trigger.

- BEFORE UPDATE OF salary ON employees specifies that the trigger will fire before an update operation on the salary column of the employees table.

- FOR EACH ROW indicates that the trigger will be executed once for each row affected by the update.

- :OLD and :NEW are references to the old and new values of the column being updated, respectively.

- The trigger inserts a record into the salary_audit table whenever a salary update occurs, recording the old salary, new salary, employee ID, and the date of the update.

Real-life Scenario:

In a real-life scenario, this trigger could be used in a payroll system to track changes in employee salaries for auditing purposes. Whenever HR or an authorized user updates an employee's salary, the trigger automatically logs the changes into an audit table, maintaining a history of salary modifications. This helps in ensuring transparency, accountability, and compliance with regulations regarding salary adjustments within the organization. Additionally, it provides a mechanism for tracking salary changes over time for reporting and analysis purposes.