Triggers_insert_update_and_delete_Scripts.sql

-- =========================================================
-- FILE: insert_update_delete_triggers.sql
-- PROJECT: TechSphere Solutions Database System
-- AUTHOR: Benard Onyango Omoga
-- DATABASE: MySQL
-- DESCRIPTION:
-- Demonstrates INSERT, UPDATE, and DELETE Triggers
-- in MySQL using the TechSphere database schema.
-- =========================================================


-- =========================================================
-- USE DATABASE
-- =========================================================
USE techsphere_solutions;


-- =========================================================
-- SECTION 1: INTRODUCTION TO TRIGGERS
-- PURPOSE:
-- Triggers automatically execute SQL code
-- when INSERT, UPDATE, or DELETE operations occur.
-- =========================================================

-- TYPES OF TRIGGERS:
-- 1. INSERT Trigger
-- 2. UPDATE Trigger
-- 3. DELETE Trigger


-- =========================================================
-- SECTION 2: CREATE AUDIT TABLES
-- PURPOSE:
-- Store logs and backup records generated by triggers
-- =========================================================


-- Audit log table
CREATE TABLE IF NOT EXISTS employee_audit_log
(
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    action_type VARCHAR(20),
    action_description VARCHAR(255),
    action_timestamp DATETIME
);


-- Deleted employee archive table
CREATE TABLE IF NOT EXISTS deleted_employees_archive
(
    archive_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    gender VARCHAR(20),
    date_of_birth DATE,
    hire_date DATE,
    department_id INT,
    deleted_at DATETIME
);


-- =========================================================
-- SECTION 3: INSERT TRIGGER
-- PURPOSE:
-- Automatically log newly inserted employees
-- =========================================================

DELIMITER $$

CREATE TRIGGER employee_after_insert

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

    INSERT INTO employee_audit_log
    (
        employee_id,
        first_name,
        last_name,
        action_type,
        action_description,
        action_timestamp
    )

    VALUES
    (
        NEW.employee_id,
        NEW.first_name,
        NEW.last_name,
        'INSERT',
        'New employee record created',
        NOW()
    );

END $$

DELIMITER ;


-- =========================================================
-- SECTION 4: TEST INSERT TRIGGER
-- PURPOSE:
-- Insert employee and verify trigger execution
-- =========================================================

INSERT INTO employees
(
    employee_id,
    first_name,
    last_name,
    gender,
    date_of_birth,
    hire_date,
    department_id
)

VALUES
(
    200,
    'Benard',
    'Omoga',
    'Male',
    '1995-04-10',
    '2026-01-15',
    2
);


-- Verify employee insertion
SELECT *
FROM employees
WHERE employee_id = 200;


-- Verify audit log
SELECT *
FROM employee_audit_log
WHERE employee_id = 200;

-- WHAT HAPPENS:
-- 1. Employee record is inserted
-- 2. INSERT trigger fires automatically
-- 3. Audit log record is created


-- =========================================================
-- SECTION 5: UPDATE TRIGGER
-- PURPOSE:
-- Track employee record updates automatically
-- =========================================================

DELIMITER $$

CREATE TRIGGER employee_after_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

    INSERT INTO employee_audit_log
    (
        employee_id,
        first_name,
        last_name,
        action_type,
        action_description,
        action_timestamp
    )

    VALUES
    (
        NEW.employee_id,
        NEW.first_name,
        NEW.last_name,
        'UPDATE',
        CONCAT(
            'Employee record updated. Previous department: ',
            OLD.department_id,
            ', New department: ',
            NEW.department_id
        ),
        NOW()
    );

END $$

DELIMITER ;


-- =========================================================
-- SECTION 6: TEST UPDATE TRIGGER
-- PURPOSE:
-- Update employee department and verify trigger
-- =========================================================

UPDATE employees

SET department_id = 3

WHERE employee_id = 200;


-- Verify updated employee
SELECT *
FROM employees
WHERE employee_id = 200;


-- Verify update audit log
SELECT *
FROM employee_audit_log
WHERE employee_id = 200
ORDER BY audit_id DESC;

-- WHAT HAPPENS:
-- 1. Employee department changes
-- 2. UPDATE trigger executes automatically
-- 3. OLD and NEW values are captured
-- 4. Audit trail is created


-- =========================================================
-- SECTION 7: DELETE TRIGGER
-- PURPOSE:
-- Archive employee data before deletion
-- =========================================================

DELIMITER $$

CREATE TRIGGER employee_before_delete

BEFORE DELETE ON employees

FOR EACH ROW

BEGIN

    INSERT INTO deleted_employees_archive
    (
        employee_id,
        first_name,
        last_name,
        gender,
        date_of_birth,
        hire_date,
        department_id,
        deleted_at
    )

    VALUES
    (
        OLD.employee_id,
        OLD.first_name,
        OLD.last_name,
        OLD.gender,
        OLD.date_of_birth,
        OLD.hire_date,
        OLD.department_id,
        NOW()
    );

END $$

DELIMITER ;


-- =========================================================
-- SECTION 8: TEST DELETE TRIGGER
-- PURPOSE:
-- Delete employee and verify archive backup
-- =========================================================

DELETE FROM employees
WHERE employee_id = 200;


-- Verify deletion
SELECT *
FROM employees
WHERE employee_id = 200;


-- Verify archived record
SELECT *
FROM deleted_employees_archive
WHERE employee_id = 200;

-- WHAT HAPPENS:
-- 1. Employee deletion starts
-- 2. BEFORE DELETE trigger executes
-- 3. Employee data is archived
-- 4. Original record is deleted safely


-- =========================================================
-- SECTION 9: VIEW COMPLETE AUDIT HISTORY
-- PURPOSE:
-- Review all trigger activities
-- =========================================================

SELECT *
FROM employee_audit_log
ORDER BY action_timestamp DESC;

-- WHAT IT DOES:
-- Displays:
-- - INSERT activities
-- - UPDATE activities
-- - Timestamps
-- - Employee tracking history


-- =========================================================
-- KEY CONCEPT SUMMARY
-- =========================================================

-- TRIGGER:
-- Automatic SQL program executed on table events

-- INSERT TRIGGER:
-- Runs after inserting records

-- UPDATE TRIGGER:
-- Runs after updating records

-- DELETE TRIGGER:
-- Runs before or after deleting records

-- NEW:
-- Refers to new inserted/updated values

-- OLD:
-- Refers to previous existing values

-- FOR EACH ROW:
-- Executes trigger once per affected row


-- =========================================================
-- BUSINESS USE CASES
-- =========================================================

-- INSERT TRIGGERS:
-- - Audit logging
-- - User activity tracking
-- - Automatic notifications

-- UPDATE TRIGGERS:
-- - Change tracking
-- - Compliance monitoring
-- - Salary history management

-- DELETE TRIGGERS:
-- - Backup before deletion
-- - Data recovery support
-- - Historical archiving


-- =========================================================
-- IMPORTANT NOTES
-- =========================================================

-- AFTER INSERT:
-- Runs after successful insertion

-- AFTER UPDATE:
-- Runs after successful update

-- BEFORE DELETE:
-- Runs before actual deletion

-- Triggers improve:
-- - Automation
-- - Security
-- - Data integrity
-- - Auditability


-- =========================================================
-- END OF FILE
-- =========================================================