BEFORE UPDATE Trigger Notes

An AFTER UPDATE trigger in Oracle is a type of trigger that automatically executes before an update operation is carried out on a table or view. It is primarily used for operations such as data validation, pre-processing, modifying data before it gets committed, or enforcing business rules before the changes take place.

In this guide, we'll go over the core aspects of BEFORE UPDATE triggers in Oracle, how to create them, and typical use cases.

1. What is a BEFORE UPDATE Trigger?

A BEFORE UPDATE trigger is executed before an UPDATE statement modifies a row in a table. This trigger allows you to:

  • Modify values before they are updated in the table.
  • Validate data before it's written to the database.
  • Enforce business rules or business logic checks.
  • Prevent invalid data from being updated (e.g., check if the new value meets certain conditions before updating).

2. Syntax of BEFORE UPDATE Trigger

The basic syntax for creating a BEFORE UPDATE trigger in Oracle is:

CREATE [OR REPLACE] TRIGGER trigger_name

BEFORE UPDATE

ON table_name

[FOR EACH ROW]

DECLARE

  -- Optional declaration section

BEGIN

  -- Trigger logic (PL/SQL code)

END;

  • trigger_name: The name of the trigger.
  • BEFORE UPDATE: Specifies that the trigger will fire before the UPDATE operation.
  • table_name: The name of the table to which the trigger is attached.
  • FOR EACH ROW: This clause makes the trigger row-level. It fires for each individual row that is updated. If omitted, the trigger will be statement-level, meaning it will fire only once for the entire update statement, regardless of the number of rows affected.

3. Trigger Timing and Execution

  • The BEFORE UPDATE trigger fires before the update operation takes place, which means it allows you to:
    • Modify the values in the row that will be updated.
    • Prevent the update operation from proceeding (e.g., if a condition isn't met).
    • Perform any business logic checks or validations.
  • Once the BEFORE UPDATE trigger executes successfully, the actual UPDATE operation will proceed. If the trigger raises an exception, the UPDATE will be canceled.

4. The :OLD and :NEW Keywords

In a BEFORE UPDATE trigger, Oracle provides two important pseudo-records:

  • :OLD: Represents the old (original) values of the row before the update.
  • :NEW: Represents the new (updated) values that are being applied to the row.

For example, you can compare the old and new values to ensure that the update is valid, or you can modify the :NEW values before they are committed.

Example:

CREATE OR REPLACE TRIGGER check_employee_update

BEFORE UPDATE

ON employees

FOR EACH ROW

BEGIN

  IF :NEW.salary < :OLD.salary THEN

    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');

  END IF;

END;

In this example:

  • The BEFORE UPDATE trigger checks if the new salary (:NEW.salary) is less than the old salary (:OLD.salary).
  • If it is, an error is raised, and the update operation is prevented.

5. Row-Level vs Statement-Level Triggers

  • Row-Level Trigger: Fires once for each row being updated (using FOR EACH ROW). This is useful when you need to access both the old and new values for each row individually.
  • Statement-Level Trigger: Fires once for the entire UPDATE statement (no FOR EACH ROW clause). This can be useful for operations that don't depend on individual row data.

Example of Statement-Level BEFORE UPDATE Trigger:

CREATE OR REPLACE TRIGGER before_update_sales

BEFORE UPDATE

ON sales

BEGIN

  -- Check if sales update is justified (based on certain conditions)

  -- Business logic goes here

END;

 

6. Modifying Data Before It Is Updated

One key benefit of a BEFORE UPDATE trigger is the ability to modify the data that will be updated. The :NEW pseudo-record can be used to change the values that are about to be written to the table.

Example: Modifying Data in BEFORE UPDATE Trigger:

CREATE OR REPLACE TRIGGER update_employee_position

BEFORE UPDATE

ON employees

FOR EACH ROW

BEGIN

  -- Automatically assign a default position if the new position is NULL

  IF :NEW.position IS NULL THEN

    :NEW.position := 'General Staff';

  END IF;

END;

In this case:

  • If the position value is NULL in the update operation, the trigger assigns a default value ('General Staff') before the row is updated.

7. Preventing Invalid Updates

A BEFORE UPDATE trigger can be used to prevent an update operation from happening if certain conditions aren't met. You can raise an exception to stop the update.

Example: Preventing Update Based on Condition:

CREATE OR REPLACE TRIGGER prevent_invalid_update

BEFORE UPDATE

ON employees

FOR EACH ROW

BEGIN

  -- Prevent update if the employee's status is 'Inactive'

  IF :OLD.status = 'Inactive' THEN

    RAISE_APPLICATION_ERROR(-20002, 'Inactive employees cannot be updated.');

  END IF;

END;

In this example:

  • The trigger prevents updates to any employee whose status was Inactive before the update, raising an error if such an attempt is made.

8. Trigger Compilation and Validity

Once the BEFORE UPDATE trigger is created, Oracle will automatically compile it. If there are errors in the PL/SQL block, Oracle will show an error message, and the trigger will not be created. You can check the status of triggers by querying the USER_TRIGGERS view.

Example: Querying Trigger Status

SELECT trigger_name, status FROM user_triggers;

  • Valid trigger: Will have a status of ENABLED.
  • Invalid trigger: Will have a status of DISABLED.

9. Use Cases for BEFORE UPDATE Triggers

Common use cases for BEFORE UPDATE triggers include:

  • Validating data: Ensuring that the updated data meets business rules (e.g., ensuring a salary update doesn't result in a value below the minimum wage).
  • Modifying data before it is updated: Automatically adjusting the data, such as setting default values for NULL fields.
  • Preventing invalid updates: Restricting updates based on certain conditions (e.g., preventing updates on records that are in a certain status).
  • Maintaining audit trails: Recording who made the update and when (though this can also be done with AFTER UPDATE triggers).
  • Performing calculations or adjustments: Making adjustments to related tables or data before the update takes place.

10. Performance Considerations

  • Performance Impact: Triggers add some overhead to DML operations. An update will take slightly longer if a BEFORE UPDATE trigger is in place, especially if the trigger logic involves complex computations or modifications.
  • Row-Level Triggers: If the update affects many rows, the trigger will execute once per row, potentially leading to performance degradation in bulk operations. It's important to keep the logic within the trigger as efficient as possible.

To optimize performance, ensure that the logic inside the trigger is minimal and efficient. If needed, you can also disable the trigger temporarily for bulk operations.

11. Example: BEFORE UPDATE Trigger with Validation and Modification

Let's look at a more complete example that combines validation and modification in a BEFORE UPDATE trigger:

CREATE OR REPLACE TRIGGER validate_and_modify_employee_update

BEFORE UPDATE

ON employees

FOR EACH ROW

BEGIN

  -- Ensure that the new salary is not less than the old salary

  IF :NEW.salary < :OLD.salary THEN

    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');

  END IF;

 

  -- Modify the department if it's being set to NULL

  IF :NEW.department IS NULL THEN

    :NEW.department := :OLD.department;  -- Retain the old department

  END IF;

 

  -- Automatically log a change in employee position

  INSERT INTO employee_position_log (emp_id, old_position, new_position, change_date)

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

END;

In this example:

  • The trigger prevents salary reductions.
  • If the department is updated to NULL, it reverts to the old department.
  • The trigger logs the position change to a separate log table (employee_position_log).

 

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)