DML Triggers Notes

DML (Data Manipulation Language) triggers in Oracle are used to automatically execute actions in response to specific events on a table or view. These events are typically DML operations such as INSERT, UPDATE, or DELETE. Triggers help maintain data integrity, enforce business rules, and automate certain tasks without requiring explicit calls to stored procedures.

Key Concepts of DML Triggers

1.     Trigger Types:

o   Before Triggers: Executes before the triggering DML operation (e.g., before an INSERT, UPDATE, or DELETE operation on a table).

o   After Triggers: Executes after the DML operation has been completed on a table.

o   Instead of Triggers: Typically used with views, this trigger is invoked instead of performing the actual DML operation on the view. It is mainly used for views that cannot be directly updated.

2.     Trigger Events: The three primary events for DML triggers are:

o   INSERT: When a row is added to the table.

o   UPDATE: When an existing row is modified.

o   DELETE: When a row is deleted from the table.

3.     Trigger Timing:

o   BEFORE: The trigger is executed before the DML action takes place.

o   AFTER: The trigger is executed after the DML action has been completed.

4.     Trigger Firing:

o   Triggers are fired row-by-row (ROW LEVEL) or statement-by-statement (STATEMENT LEVEL) depending on how the trigger is defined.

o   Row-Level Triggers: These are executed for each row affected by the DML operation. If an UPDATE affects 10 rows, the trigger will fire 10 times.

o   Statement-Level Triggers: These are executed once for the entire DML operation, regardless of how many rows are affected.

Components of DML Triggers

1.     Trigger Body: The actual PL/SQL code that runs when the trigger is fired. This can include conditional logic, data manipulation, or calling other procedures.

2.     Trigger Action: Defines what happens when the trigger fires (e.g., log an event, modify data, raise an exception).

3.     NEW and OLD References:

o   NEW: Refers to the new values of a row being inserted or updated. For INSERT, it refers to the row being inserted; for UPDATE, it refers to the new values of the row.

o   OLD: Refers to the old values of the row being updated or deleted. For UPDATE, it refers to the old values of the row before the update; for DELETE, it refers to the values of the row being deleted.

Syntax for DML Triggers

CREATE [OR REPLACE] TRIGGER trigger_name
   {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
   ON table_name
   [FOR EACH ROW]
   [WHEN (condition)]
DECLARE
   -- Declaration of variables
BEGIN
   -- Trigger action (PL/SQL code)
EXCEPTION
   -- Exception handling code (if needed)
END;

Key Parts of the Syntax:

·        trigger_name: The name of the trigger.

·        Trigger Time: BEFORE, AFTER, INSTEAD OF.

·        Trigger Event: INSERT, UPDATE, or DELETE.

·        ON table_name: The table or view the trigger is attached to.

·        FOR EACH ROW: Indicates that the trigger is row-level.

·        WHEN (condition): Optional. Defines a condition under which the trigger will execute.

Examples of DML Triggers

1. BEFORE INSERT Trigger

This example shows a trigger that automatically populates a "created_date" column with the current date when a row is inserted into the employees table.

CREATE OR REPLACE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   :NEW.created_date := SYSDATE;
END;
/

In this case, :NEW refers to the newly inserted row, and created_date is set to the current date.

2. AFTER UPDATE Trigger

This example demonstrates an AFTER UPDATE trigger that updates a "last_updated" column every time an employee's record is modified.

CREATE OR REPLACE TRIGGER after_update_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   :NEW.last_updated := SYSDATE;
END;
/

Here, :NEW refers to the updated row, and the last_updated column is set to the current date whenever the record is updated.

3. BEFORE DELETE Trigger

This example demonstrates a trigger that will prevent deletion of a record if the salary of an employee is greater than 100,000.

CREATE OR REPLACE TRIGGER before_delete_emp
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
   IF :OLD.salary > 100000 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employee with salary greater than 100,000');
   END IF;
END;
/

The trigger checks the old value of the row (i.e., the row before deletion) and raises an error if the salary is greater than 100,000, preventing the deletion.

4. INSTEAD OF Trigger (Used for Views)

This trigger is used to provide INSERT, UPDATE, and DELETE functionality for a view that doesn't directly support these operations.

CREATE OR REPLACE TRIGGER instead_of_view_trigger
INSTEAD OF INSERT ON employees_view
FOR EACH ROW
BEGIN
   INSERT INTO employees (emp_id, emp_name, emp_salary)
   VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.emp_salary);
END;
/

This trigger allows inserting data into the employees view, even if the view itself doesn't directly support inserts.

Handling Multiple DML Operations

If you need a trigger to handle multiple DML operations, such as INSERT, UPDATE, and DELETE, you can specify them in the trigger definition:

CREATE OR REPLACE TRIGGER handle_dml_operations
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
   IF INSERTING THEN
      -- Handle insert operation
   ELSIF UPDATING THEN
      -- Handle update operation
   ELSIF DELETING THEN
      -- Handle delete operation
   END IF;
END;
/

In this example, INSERTING, UPDATING, and DELETING are conditional predicates that allow you to differentiate between different DML operations.

Managing Performance with Triggers

·        Avoid Complex Logic in Triggers: Triggers should ideally contain lightweight logic to avoid negatively impacting performance.

·        Use Statement-Level Triggers for Bulk Operations: If you are dealing with large numbers of rows, using statement-level triggers can reduce the overhead caused by row-level triggers.

·        Monitor Trigger Activity: Triggers can sometimes cause unexpected results, especially in large systems, so it’s important to monitor their execution and make sure they do not lead to performance degradation.

Example: Trigger with Conditional Logic

This example shows how you can use conditions in your trigger to perform different actions depending on the operation.

CREATE OR REPLACE TRIGGER employee_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
   IF INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('New employee added: ' || :NEW.emp_name);
   ELSIF UPDATING THEN
      DBMS_OUTPUT.PUT_LINE('Employee updated: ' || :NEW.emp_name);
   ELSIF DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Employee deleted: ' || :OLD.emp_name);
   END IF;
END;
/

This trigger will print messages whenever an employee is inserted, updated, or deleted from the employees table.

Error Handling in Triggers

Triggers can also include exception handling, which is useful for catching errors and handling them gracefully. For example:

CREATE OR REPLACE TRIGGER example_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   IF :NEW.emp_salary < 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be negative.');
   END IF;
END;
/

In this trigger, we check if the salary being inserted is negative. If so, an exception is raised, and the insert operation is aborted.

Conclusion

DML triggers in Oracle are powerful tools for automating data integrity, enforcing business rules, and responding to changes in your data. However, they should be used carefully to avoid unnecessary performance issues or unwanted side effects. Understanding the different types of triggers, their timing, and how to use them efficiently is key to mastering Oracle DML triggers.

If you need examples or have further questions on specific trigger scenarios, feel free to ask!

 

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)