Oracle

In Oracle, a database trigger is a stored PL/SQL block that is automatically executed (or "triggered") in response to certain events on a particular table or view. Triggers can be used for various purposes, such as enforcing business rules, validating input data, or automatically updating related tables. They are powerful tools for ensuring data integrity and automating tasks within the database.

Here’s a brief overview of database triggers:

1. Types of Triggers

   • BEFORE Trigger: Executes before an insert, update, or delete operation on a table.
   • AFTER Trigger: Executes after an insert, update, or delete operation on a table.
   • INSTEAD OF Trigger: Executes instead of an insert, update, or delete operation, commonly used with views.

2. Creating a Trigger

   • To create a trigger, you use the CREATE TRIGGER statement. Here’s a basic example that demonstrates how to create a trigger:

   CREATE OR REPLACE TRIGGER before_book_insert
   BEFORE INSERT ON books
   FOR EACH ROW
   BEGIN
     IF :NEW.title IS NULL THEN
       RAISE_APPLICATION_ERROR(-20001, 'Title cannot be NULL');
     END IF;
   END;
   

   • This trigger, named before_book_insert, ensures that a row cannot be inserted into the books table without a title.

3. Trigger Timing

   • Triggers can be set to fire BEFORE or AFTER an operation. You specify this timing using keywords in the CREATE TRIGGER statement.

4. Trigger Event

   • Specify the event (INSERT, UPDATE, DELETE) that will activate the trigger. This is defined in the CREATE TRIGGER statement.

5. FOR EACH ROW vs. FOR EACH STATEMENT

   • FOR EACH ROW: The trigger fires once for each row affected by the SQL statement.
   • FOR EACH STATEMENT: The trigger fires once for the entire SQL statement, regardless of the number of rows affected.

6. Disabling and Dropping Triggers

   • To disable a trigger:

     ALTER TRIGGER before_book_insert DISABLE;
     

   • To drop a trigger:

     DROP TRIGGER before_book_insert;
     

7. Example: Inserting Data into books Table

   • The following SQL statement will activate the trigger, demonstrating how it prevents insertion of a book without a title:

   INSERT INTO books (book_id, title, author_id)
   VALUES (1, NULL, 101);  -- This will raise an error due to the trigger
   

This introduction to database triggers provides a foundation for understanding how they work in Oracle and how you can use them to enforce rules and automate tasks in your database environment.

    CREATE OR REPLACE TRIGGER log_new_employee
    AFTER INSERT ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO employee_logs (action, employee_id, employee_name, salary)
        VALUES ('INSERT',:NEW.id,:NEW.name,:NEW.salary);
    END;
  

    CREATE OR REPLACE TRIGGER log_employee_update
    AFTER UPDATE ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO audit_trail (action, employee_id, old_salary, new_salary)
        VALUES ('UPDATE',:OLD.id,:OLD.salary,:NEW.salary);
    END;
  

    CREATE OR REPLACE TRIGGER move_deleted_employee
    AFTER DELETE ON employees
    FOR EACH ROW
    BEGIN
        INSERT INTO deleted_employees (employee_id, employee_name, deleted_at)
        VALUES (:OLD.id,:OLD.name, SYSDATE);
    END;