SQL and I Had Beef 😂So I Built a Trigger

Introduction

Let me be honest SQL has been giving me a hard time for a while. So, I was getting stuck even with the basics. So I decided to stop running away and build a real project from scratch: a student enrollment system.

I wanted something simple but useful, where I could understand how real systems are built with SQL,,including relationships, foreign keys, and triggers. This is how it went down.

What the System Does

This project is all about managing a small school system. It handles:

• Storing student info (name, email, date of birth)
• Keeping track of instructors
• Linking courses to instructors
• Enrolling students to courses
• Logging every enrollment using a trigger

Tables I Created

SQL Code I Used

1. Students Table

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE
);

2. Instructors Table

CREATE TABLE Instructors (
    instructor_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

3. Courses Table

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor_id INT,
    FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id)
);

4. Enrollments Table

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

5. Enrollment Log Table

CREATE TABLE Enrollment_Log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    enrollment_id INT,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. Trigger to Log Enrollments

CREATE TRIGGER LogNewEnrollment
AFTER INSERT ON Enrollments
FOR EACH ROW
INSERT INTO Enrollment_Log (enrollment_id)
VALUES (NEW.enrollment_id);

Sample Data I Added

1.Instructors

INSERT INTO Instructors (instructor_id, first_name, last_name, email) VALUES 
(1, 'Thomas', 'Ndegwa', '[email protected]'),
(2, 'Lilian', 'Achieng', '[email protected]'),
(3, 'George', 'Kariuki', '[email protected]');

2. Students

INSERT INTO Students (student_id, first_name, last_name, email, date_of_birth) VALUES 
(101, 'Alice', 'Mwende', '[email protected]', '2002-04-12'),
(102, 'Brian', 'Otieno', '[email protected]', '2001-08-05');

3. Courses

INSERT INTO Courses (course_id, course_name, instructor_id) VALUES 
(201, 'Database Systems', 1),
(202, 'Python Programming', 2),
(203, 'Data Analytics', 3);

4.Enrollments

INSERT INTO Enrollments (enrollment_id, student_id, course_id, grade) VALUES 
(301, 101, 201, 'A'),
(302, 102, 202, 'B');

What I Learned

Through this project, I’ve learned:

• How to design related tables using foreign keys

• How to keep data clean and connected

• How to use triggers to automate tasks (like logging)

• The importance of thinking through the whole structure before writing code

• This is way better than just memorizing;
  

SELECT * FROM table;

What I Might Add Later

• Add user logins for students and instructors

• Store attendance and class schedules

• Build a simple dashboard with Power BI

Final Thoughts

This was a small project, but it really helped me understand SQL better. I now see why people say the database is the heart of any system.
If you're learning SQL, I encourage you to try something small like this,it makes everything more clear.