Week 4 — User-Defined Functions & Triggers¶

DELIMITER $$

CREATE FUNCTION calculate_gpa (p_student_id INT)
RETURNS DECIMAL(3,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_gpa DECIMAL(3,2);

    SELECT ROUND(AVG(points), 2)
    INTO   v_gpa
    FROM   grade_points
    WHERE  student_id = p_student_id
      AND  points IS NOT NULL;

    RETURN COALESCE(v_gpa, 0.00);
END$$

DELIMITER ;

-- Using the function in a query
SELECT student_id,
       first_name,
       last_name,
       calculate_gpa(student_id) AS computed_gpa
FROM   students
ORDER BY computed_gpa DESC;

-- String helper: extract initials from a name
DELIMITER $$
CREATE FUNCTION get_initials (p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS CHAR(3)
DETERMINISTIC
NO SQL
BEGIN
    RETURN CONCAT(UPPER(LEFT(p_first, 1)), '.', UPPER(LEFT(p_last, 1)), '.');
END$$
DELIMITER ;

-- Date helper: return the current academic semester code
CREATE FUNCTION current_semester ()
RETURNS CHAR(6)
NOT DETERMINISTIC
NO SQL
BEGIN
    RETURN CONCAT(
        IF(MONTH(NOW()) BETWEEN 1 AND 5, 'S', 'F'),
        YEAR(NOW())
    );
END$$
DELIMITER ;

-- Numeric: letter grade from numeric grade points
CREATE FUNCTION points_to_letter (p_points DECIMAL(3,2))
RETURNS CHAR(2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN CASE
        WHEN p_points >= 3.85 THEN 'A'   WHEN p_points >= 3.50 THEN 'A-'
        WHEN p_points >= 3.15 THEN 'B+'  WHEN p_points >= 2.85 THEN 'B'
        WHEN p_points >= 2.50 THEN 'B-'  WHEN p_points >= 2.15 THEN 'C+'
        WHEN p_points >= 1.85 THEN 'C'   WHEN p_points >= 1.50 THEN 'C-'
        WHEN p_points >= 1.15 THEN 'D+'  WHEN p_points >= 0.85 THEN 'D'
        ELSE 'F'
    END;
END$$
DELIMITER ;

-- Table-valued function: return all courses a student is enrolled in
CREATE OR REPLACE FUNCTION get_student_courses (p_student_id INT)
RETURNS TABLE (
    course_code VARCHAR(10),
    title       VARCHAR(150),
    credits     SMALLINT,
    semester    CHAR(6),
    grade       CHAR(2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT c.course_code, c.title, c.credits, e.semester, e.grade
        FROM   enrollments e
        INNER JOIN courses c ON c.course_id = e.course_id
        WHERE  e.student_id = p_student_id
        ORDER BY e.semester DESC, c.course_code;
END;
$$;

-- Call it like a table:
SELECT * FROM get_student_courses(1042);

-- Return full student rows matching a department
CREATE OR REPLACE FUNCTION students_in_dept (p_dept_name TEXT)
RETURNS SETOF students
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT s.*
        FROM   students    s
        INNER JOIN departments d ON d.dept_id = s.dept_id
        WHERE  d.dept_name = p_dept_name;
END;
$$;

-- Setup audit table
CREATE TABLE enrollment_audit (
    audit_id     INT          PRIMARY KEY AUTO_INCREMENT,
    action_type  ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    enrollment_id INT,
    student_id   INT,
    course_id    INT,
    old_grade    CHAR(2),
    new_grade    CHAR(2),
    changed_by   VARCHAR(100),
    changed_at   DATETIME(6)  DEFAULT NOW(6)
);

DELIMITER $$

-- Audit INSERT
CREATE TRIGGER trg_enrollment_audit_insert
AFTER INSERT ON enrollments
FOR EACH ROW
BEGIN
    INSERT INTO enrollment_audit
        (action_type, enrollment_id, student_id, course_id, new_grade, changed_by)
    VALUES
        ('INSERT', NEW.enrollment_id, NEW.student_id, NEW.course_id,
         NEW.grade, USER());
END$$

-- Audit UPDATE (only when grade changes)
CREATE TRIGGER trg_enrollment_audit_update
AFTER UPDATE ON enrollments
FOR EACH ROW
BEGIN
    IF OLD.grade != NEW.grade OR (OLD.grade IS NULL) != (NEW.grade IS NULL) THEN
        INSERT INTO enrollment_audit
            (action_type, enrollment_id, student_id, course_id,
             old_grade, new_grade, changed_by)
        VALUES
            ('UPDATE', NEW.enrollment_id, NEW.student_id, NEW.course_id,
             OLD.grade, NEW.grade, USER());
    END IF;
END$$

-- Audit DELETE
CREATE TRIGGER trg_enrollment_audit_delete
AFTER DELETE ON enrollments
FOR EACH ROW
BEGIN
    INSERT INTO enrollment_audit
        (action_type, enrollment_id, student_id, course_id, old_grade, changed_by)
    VALUES
        ('DELETE', OLD.enrollment_id, OLD.student_id, OLD.course_id,
         OLD.grade, USER());
END$$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER trg_enrollments_before_insert
BEFORE INSERT ON enrollments
FOR EACH ROW
BEGIN
    DECLARE v_existing_credits INT;
    DECLARE v_course_credits   INT;

    -- Rule 1: Semester must be a valid format (F or S + 4-digit year)
    IF NEW.semester NOT REGEXP '^[FS][0-9]{4}$' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Invalid semester format. Use F2025 or S2025.';
    END IF;

    -- Rule 2: Student cannot exceed 18 credits per semester
    SELECT COALESCE(SUM(c.credits), 0)
    INTO   v_existing_credits
    FROM   enrollments e
    INNER JOIN courses c ON c.course_id = e.course_id
    WHERE  e.student_id = NEW.student_id
      AND  e.semester   = NEW.semester;

    SELECT credits INTO v_course_credits
    FROM   courses WHERE course_id = NEW.course_id;

    IF v_existing_credits + v_course_credits > 18 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Enrollment would exceed 18-credit semester limit.';
    END IF;

    -- Rule 3: Auto-fill instructor_id if not provided
    IF NEW.instructor_id IS NULL THEN
        SELECT i.instructor_id INTO NEW.instructor_id
        FROM   instructors i
        INNER JOIN courses c ON c.dept_id = i.dept_id
        WHERE  c.course_id = NEW.course_id
        LIMIT  1;
    END IF;
END$$

DELIMITER ;

-- Add tracking columns to students (if not already present)
ALTER TABLE students
    ADD COLUMN created_at  DATETIME DEFAULT NOW(),
    ADD COLUMN updated_at  DATETIME DEFAULT NOW() ON UPDATE NOW(),
    ADD COLUMN updated_by  VARCHAR(100);

DELIMITER $$

CREATE TRIGGER trg_students_set_updated
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW(6);
    SET NEW.updated_by = USER();
END$$

DELIMITER ;

-- Add a cached enrollment count column to courses
ALTER TABLE courses ADD COLUMN enrolled_count INT DEFAULT 0;

DELIMITER $$

CREATE TRIGGER trg_enroll_count_insert
AFTER INSERT ON enrollments
FOR EACH ROW
    UPDATE courses SET enrolled_count = enrolled_count + 1
    WHERE  course_id = NEW.course_id$$

CREATE TRIGGER trg_enroll_count_delete
AFTER DELETE ON enrollments
FOR EACH ROW
    UPDATE courses SET enrolled_count = enrolled_count - 1
    WHERE  course_id = OLD.course_id$$

-- Note: MySQL 8.0 allows single-statement triggers without BEGIN/END
DELIMITER ;

CREATE TABLE enrollments_archive LIKE enrollments;
ALTER TABLE enrollments_archive
    ADD COLUMN archived_at DATETIME DEFAULT NOW(),
    ADD COLUMN archived_by VARCHAR(100);

DELIMITER $$

CREATE TRIGGER trg_enrollments_archive_delete
BEFORE DELETE ON enrollments
FOR EACH ROW
BEGIN
    INSERT INTO enrollments_archive
        (enrollment_id, student_id, course_id, instructor_id,
         semester, grade, archived_at, archived_by)
    VALUES
        (OLD.enrollment_id, OLD.student_id, OLD.course_id, OLD.instructor_id,
         OLD.semester, OLD.grade, NOW(6), USER());
END$$

DELIMITER ;

-- Create a view joining students and departments
CREATE VIEW student_dept_view AS
    SELECT s.student_id, s.first_name, s.last_name,
           s.gpa, d.dept_name
    FROM   students s
    INNER JOIN departments d ON d.dept_id = s.dept_id;

-- Allow UPDATE through the view
CREATE OR REPLACE FUNCTION upd_student_dept_view_fn()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE students
    SET    first_name = NEW.first_name,
           last_name  = NEW.last_name,
           gpa        = NEW.gpa
    WHERE  student_id = OLD.student_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_instead_of_update
INSTEAD OF UPDATE ON student_dept_view
FOR EACH ROW EXECUTE FUNCTION upd_student_dept_view_fn();

-- DANGER: This trigger updates students, which could fire another trigger on students
CREATE TRIGGER trg_after_grade_change
AFTER UPDATE ON enrollments
FOR EACH ROW
BEGIN
    -- This UPDATE on students may fire trg_students_set_updated
    UPDATE students SET gpa = calculate_gpa(NEW.student_id)
    WHERE  student_id = NEW.student_id;
    -- If trg_students_set_updated also updates enrollments → infinite loop!
END;

-- Soft-delete pattern: prevent hard deletes on students; use a deleted_at column instead
ALTER TABLE students ADD COLUMN deleted_at DATETIME DEFAULT NULL;

DELIMITER $$

CREATE TRIGGER trg_students_prevent_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    -- Allow deletion only if student has never enrolled
    IF EXISTS (SELECT 1 FROM enrollments WHERE student_id = OLD.student_id LIMIT 1) THEN
        -- Soft-delete instead of hard delete
        UPDATE students SET deleted_at = NOW(6) WHERE student_id = OLD.student_id;
        -- Signal cancels the DELETE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Student has enrollment history — record soft-deleted instead.';
    END IF;
    -- If no enrollments, allow the hard delete to proceed (no SIGNAL)
END$$

DELIMITER ;

-- Essential string functions used in university database context
SELECT
    CONCAT(first_name, ' ', last_name)         AS full_name,
    UPPER(last_name)                            AS last_upper,
    LOWER(email)                                AS email_norm,
    LENGTH(email)                               AS email_len,
    CHAR_LENGTH(first_name)                     AS fname_chars,   -- multi-byte safe
    TRIM(BOTH ' ' FROM '  Alice  ')             AS trimmed,
    SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username,
    REPLACE(course_code, 'ITEC', 'CS')          AS new_code,
    LPAD(student_id, 8, '0')                    AS padded_id,
    REGEXP_REPLACE(email, '[^a-z0-9@.]', '')    AS sanitized_email
FROM students s
INNER JOIN courses c ON c.dept_id = s.dept_id
LIMIT 5;

SELECT
    ROUND(gpa, 1)                   AS gpa_rounded,
    TRUNCATE(gpa, 1)                AS gpa_truncated,  -- no rounding
    ABS(gpa - 3.0)                  AS distance_from_3,
    MOD(student_id, 10)             AS id_last_digit,
    POWER(gpa, 2)                   AS gpa_squared,
    GREATEST(gpa, 2.0)              AS at_least_2,
    LEAST(gpa, 4.0)                 AS capped_at_4,
    FORMAT(gpa, 2)                  AS formatted_str,  -- locale-aware, returns string
    COALESCE(gpa, 0.0)              AS gpa_or_zero
FROM students;

SELECT
    NOW()                                               AS current_datetime,
    CURDATE()                                           AS today,
    DATE_FORMAT(hire_date, '%M %d, %Y')                 AS hire_display,
    DATEDIFF(CURDATE(), hire_date)                      AS days_employed,
    TIMESTAMPDIFF(YEAR, hire_date, CURDATE())           AS years_employed,
    DATE_ADD(CURDATE(), INTERVAL 1 SEMESTER)            AS -- not valid
    LAST_DAY(CURDATE())                                 AS end_of_month,
    DAYNAME(hire_date)                                  AS hire_weekday,
    WEEK(CURDATE(), 1)                                  AS iso_week,
    EXTRACT(YEAR FROM hire_date)                        AS hire_year
FROM instructors;

-- Students table with a JSON preferences column
ALTER TABLE students ADD COLUMN preferences JSON;

UPDATE students SET preferences = '{"theme":"dark","notifications":true,"lang":"en"}'
WHERE student_id = 1001;

SELECT
    student_id,
    JSON_EXTRACT(preferences, '$.theme')           AS theme,
    preferences->>'$.lang'                         AS lang,           -- shorthand
    JSON_CONTAINS(preferences, '"true"', '$.notifications') AS notif_on,
    JSON_KEYS(preferences)                         AS pref_keys,
    JSON_SET(preferences, '$.theme', 'light')      AS updated_prefs
FROM students
WHERE preferences IS NOT NULL;

SELECT
    student_id,
    gpa,
    IF(gpa >= 3.5, 'Honor Roll', 'Standard')           AS status,
    IFNULL(gpa, 0.00)                                  AS gpa_safe,
    NULLIF(grade, 'W')                                 AS non_withdrawal_grade, -- NULL if W
    CASE
        WHEN gpa >= 3.9 THEN 'Summa'
        WHEN gpa >= 3.7 THEN 'Magna'
        WHEN gpa >= 3.5 THEN 'Cum Laude'
        ELSE 'Standard'
    END                                                AS honor_status,
    COALESCE(grade, 'In Progress', 'Unknown')          AS grade_display
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id AND e.semester = 'F2025';