Advertise here ✔️

SALEHE NJOHOLE

Phone: +255 692 127 931 Email: njoholes@gmail.com

Tuesday, December 2, 2025

Home » » SQL TRIGGERS

SQL TRIGGERS

By  No comments:

 C:\Users\ponjo>mysql -u root -p

Enter password:


MariaDB [(none)]> create database uni;

Query OK, 1 row affected (0.003 sec)


MariaDB [(none)]> create table students (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> age INT,

    -> gender ENUM('F','M'),

    -> ;

ERROR 1046 (3D000): No database selected

MariaDB [(none)]>

MariaDB [(none)]> use uni;

Database changed

MariaDB [uni]> CREATE TABLE courses (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> course_name VARCHAR(100)

    -> );

Query OK, 0 rows affected (0.013 sec)


MariaDB [uni]> CREATE TABLE department (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> dept_name VARCHAR(100)

    -> );

Query OK, 0 rows affected (0.018 sec)


MariaDB [uni]> CREATE TABLE students (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> age INT,

    -> gender ENUM('F','M'),

    -> course_id INT,

    -> dept_id INT,

    -> FOREIGN KEY (course_id) REFERENCES courses(id),

    -> FOREIGN KEY (dept_id) REFERENCES department(id)

    -> );

Query OK, 0 rows affected (0.038 sec)


MariaDB [uni]> CREATE TABLE grades (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> grades VARCHAR(100),

    -> FOREIGN KEY (student_id) REFERENCES students(id),

    -> FOREIGN KEY (course_id) REFERENCES courses(id)

    -> );

Query OK, 0 rows affected (0.048 sec)


MariaDB [uni]> SHOW TABLES;

+---------------+

| Tables_in_uni |

+---------------+

| courses       |

| department    |

| grades        |

| students      |

+---------------+

4 rows in set (0.001 sec)


MariaDB [uni]> DESCRIBE courses;

+-------------+--------------+------+-----+---------+----------------+

| Field       | Type         | Null | Key | Default | Extra          |

+-------------+--------------+------+-----+---------+----------------+

| id          | int(11)      | NO   | PRI | NULL    | auto_increment |

| course_name | varchar(100) | YES  |     | NULL    |                |

+-------------+--------------+------+-----+---------+----------------+

2 rows in set (0.034 sec)


MariaDB [uni]> INSERT INTO courses(course_name) VALUES (

    -> 'BIT'),('ISM'),('SE'),('CS');

Query OK, 4 rows affected (0.063 sec)

Records: 4  Duplicates: 0  Warnings: 0


MariaDB [uni]> DESCRIBE departments;

ERROR 1146 (42S02): Table 'uni.departments' doesn't exist

MariaDB [uni]> DESCRIBE department;

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| id        | int(11)      | NO   | PRI | NULL    | auto_increment |

| dept_name | varchar(100) | YES  |     | NULL    |                |

+-----------+--------------+------+-----+---------+----------------+

2 rows in set (0.031 sec)


MariaDB [uni]> INSERT INTO department(dept_name) VALUES (

    -> 'ICT'),('ACCOUNTS'),('HR'),('FINANCE');

Query OK, 4 rows affected (0.008 sec)

Records: 4  Duplicates: 0  Warnings: 0


MariaDB [uni]> DESCRIBE students;

+-----------+---------------+------+-----+---------+----------------+

| Field     | Type          | Null | Key | Default | Extra          |

+-----------+---------------+------+-----+---------+----------------+

| id        | int(11)       | NO   | PRI | NULL    | auto_increment |

| fname     | varchar(100)  | YES  |     | NULL    |                |

| age       | int(11)       | YES  |     | NULL    |                |

| gender    | enum('F','M') | YES  |     | NULL    |                |

| course_id | int(11)       | YES  | MUL | NULL    |                |

| dept_id   | int(11)       | YES  | MUL | NULL    |                |

+-----------+---------------+------+-----+---------+----------------+

6 rows in set (0.032 sec)


MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Mary',32,'F',2,1);

Query OK, 1 row affected (0.031 sec)


MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Jenny',28,'F',3,3),('Juma',40,'M',4,4);

Query OK, 2 rows affected (0.013 sec)

Records: 2  Duplicates: 0  Warnings: 0


MariaDB [uni]> DESCRIBE grades;

+------------+--------------+------+-----+---------+----------------+

| Field      | Type         | Null | Key | Default | Extra          |

+------------+--------------+------+-----+---------+----------------+

| id         | int(11)      | NO   | PRI | NULL    | auto_increment |

| student_id | int(11)      | YES  | MUL | NULL    |                |

| course_id  | int(11)      | YES  | MUL | NULL    |                |

| grades     | varchar(100) | YES  |     | NULL    |                |

+------------+--------------+------+-----+---------+----------------+

4 rows in set (0.025 sec)


MariaDB [uni]> INSERT INTO grades(student_id,course_id,grades) VALUES (

    -> 1,2,'A'),(2,3,'C'),(3,4,'B+');

Query OK, 3 rows affected (0.008 sec)

Records: 3  Duplicates: 0  Warnings: 0


MariaDB [uni]> SELECT * FROM courses;

+----+-------------+

| id | course_name |

+----+-------------+

|  1 | BIT         |

|  2 | ISM         |

|  3 | SE          |

|  4 | CS          |

+----+-------------+

4 rows in set (0.000 sec)


MariaDB [uni]> SELECT * FROM department;

+----+-----------+

| id | dept_name |

+----+-----------+

|  1 | ICT       |

|  2 | ACCOUNTS  |

|  3 | HR        |

|  4 | FINANCE   |

+----+-----------+

4 rows in set (0.000 sec)


MariaDB [uni]> SELECT * FROM students;

+----+-------+------+--------+-----------+---------+

| id | fname | age  | gender | course_id | dept_id |

+----+-------+------+--------+-----------+---------+

|  1 | Mary  |   32 | F      |         2 |       1 |

|  2 | Jenny |   28 | F      |         3 |       3 |

|  3 | Juma  |   40 | M      |         4 |       4 |

+----+-------+------+--------+-----------+---------+

3 rows in set (0.000 sec)


MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.001 sec)


MariaDB [uni]> ALTER TABLE students ADD COLUMN created_at DATETIME;

Query OK, 0 rows affected (0.011 sec)

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [uni]> SELECT * FROM students;

+----+-------+------+--------+-----------+---------+------------+

| id | fname | age  | gender | course_id | dept_id | created_at |

+----+-------+------+--------+-----------+---------+------------+

|  1 | Mary  |   32 | F      |         2 |       1 | NULL       |

|  2 | Jenny |   28 | F      |         3 |       3 | NULL       |

|  3 | Juma  |   40 | M      |         4 |       4 | NULL       |

+----+-------+------+--------+-----------+---------+------------+

3 rows in set (0.001 sec)


MariaDB [uni]> CREATE TRIGGER student_insert BEFORE INSERT ON students

    -> FOR EACH ROW SET NEW.created_at = NOW();

Query OK, 0 rows affected (0.011 sec)


MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('Jackline',20,'F',3,3);

Query OK, 1 row affected (0.010 sec)


MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          |

+----+----------+------+--------+-----------+---------+---------------------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 |

+----+----------+------+--------+-----------+---------+---------------------+

4 rows in set (0.001 sec)


MariaDB [uni]> SHOW TRIGGERS;

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| Trigger        | Event  | Table    | Statement                  | Timing | Created                | sql_mode                                            | Definer        | character_set_client | collation_connection | Database Collation |

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

| student_insert | INSERT | students | SET NEW.created_at = NOW() | BEFORE | 2025-12-01 22:11:40.34 | NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION | root@localhost | cp850                | cp850_general_ci     | utf8mb4_general_ci |

+----------------+--------+----------+----------------------------+--------+------------------------+-----------------------------------------------------+----------------+----------------------+----------------------+--------------------+

1 row in set (0.024 sec)


MariaDB [uni]> CREATE TABLE student_backups (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> fname VARCHAR(100),

    -> course_id INT,

    -> dept_id INT

    -> );

Query OK, 0 rows affected (0.019 sec)


MariaDB [uni]> CREATE TRIGGER student_backup AFTER INSERT ON students

    -> FOR EACH ROW INSERT INTO student_backups(fname,course_id,dept_id)

    -> VALUES (NEW.fname,NEW.course_id,NEW.dept.id);

Query OK, 0 rows affected (0.015 sec)


MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('John',30,'M',3,3);

ERROR 1054 (42S22): Unknown column 'NEW.dept.id' in 'field list'

MariaDB [uni]> DROP TRIGGER student_backup;

Query OK, 0 rows affected (0.012 sec)


MariaDB [uni]> CREATE TRIGGER student_backup AFTER INSERT ON students

    -> FOR EACH ROW INSERT INTO student_backups(fname,course_id,dept_id)

    -> VALUES (NEW.fname,NEW.course_id,NEW.dept_id);

Query OK, 0 rows affected (0.017 sec)


MariaDB [uni]> INSERT INTO students(fname,age,gender,course_id,dept_id)

    -> VALUES ('John',30,'M',3,3);

Query OK, 1 row affected (0.010 sec)


MariaDB [uni]> SELECT * FROM student_backups;

+----+-------+-----------+---------+

| id | fname | course_id | dept_id |

+----+-------+-----------+---------+

|  1 | John  |         3 |       3 |

+----+-------+-----------+---------+

1 row in set (0.000 sec)


MariaDB [uni]> SELECT * students;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'students' at line 1

MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          |

+----+----------+------+--------+-----------+---------+---------------------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 |

+----+----------+------+--------+-----------+---------+---------------------+

5 rows in set (0.000 sec)


MariaDB [uni]> ALTER TABLE students ADD COLUMN update_at DATETIME;

Query OK, 0 rows affected (0.015 sec)

Records: 0  Duplicates: 0  Warnings: 0


MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+-----------+

| id | fname    | age  | gender | course_id | dept_id | created_at          | update_at |

+----+----------+------+--------+-----------+---------+---------------------+-----------+

|  1 | Mary     |   32 | F      |         2 |       1 | NULL                | NULL      |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                | NULL      |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                | NULL      |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 | NULL      |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 | NULL      |

+----+----------+------+--------+-----------+---------+---------------------+-----------+

5 rows in set (0.001 sec)


MariaDB [uni]> CREATE TRIGGER update_student BEFORE UPDATE ON students

    -> FOR EACH ROW SET NEW.update_at = NOW();

Query OK, 0 rows affected (0.013 sec)


MariaDB [uni]> UPDATE students SET fname = 'Mariam' WHERE id=1;

Query OK, 1 row affected (0.011 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [uni]> SELECT * FROM students;

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

| id | fname    | age  | gender | course_id | dept_id | created_at          | update_at           |

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

|  1 | Mariam   |   32 | F      |         2 |       1 | NULL                | 2025-12-01 22:32:25 |

|  2 | Jenny    |   28 | F      |         3 |       3 | NULL                | NULL                |

|  3 | Juma     |   40 | M      |         4 |       4 | NULL                | NULL                |

|  4 | Jackline |   20 | F      |         3 |       3 | 2025-12-01 22:14:51 | NULL                |

|  6 | John     |   30 | M      |         3 |       3 | 2025-12-01 22:23:06 | NULL                |

+----+----------+------+--------+-----------+---------+---------------------+---------------------+

5 rows in set (0.001 sec)


MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.001 sec)


MariaDB [uni]> CREATE TABLE grade_backup (

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> old_grade VARCHAR(100),

    -> new_grade VARCHAR(100),

    -> created_at DATETIME

    -> );

Query OK, 0 rows affected (0.015 sec)


MariaDB [uni]> CREATE TRIGGER student_update_backup AFTER UPDATE ON

    -> grades FOR EACH ROW INSERT INTO grade_backup(student_id,course_id,old_grade,new_grade,created_at)

    -> VALUES (OLD.student_id,OLD.course_id,OLD.grades,NEW.grades,NOW());

Query OK, 0 rows affected (0.017 sec)



MariaDB [uni]> UPDATE students SET fname = 'Mary' WHERE id=1;

Query OK, 1 row affected (0.002 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | A      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.004 sec)


MariaDB [uni]> UPDATE grades SET grades='C' WHERE id=1;

Query OK, 1 row affected (0.002 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [uni]> SELECT * FROM grades;

+----+------------+-----------+--------+

| id | student_id | course_id | grades |

+----+------------+-----------+--------+

|  1 |          1 |         2 | C      |

|  2 |          2 |         3 | C      |

|  3 |          3 |         4 | B+     |

+----+------------+-----------+--------+

3 rows in set (0.000 sec)


MariaDB [uni]> SELECT * FROM grade_backup;

+----+------------+-----------+-----------+-----------+---------------------+

| id | student_id | course_id | old_grade | new_grade | created_at          |

+----+------------+-----------+-----------+-----------+---------------------+

|  1 |          1 |         2 | A         | C         | 2025-12-01 22:48:15 |

+----+------------+-----------+-----------+-----------+---------------------+

1 row in set (0.000 sec)


MariaDB [uni]> CREATE TRIGGER delete_grades BEFORE DELETE ON grades

    -> FOR EACH ROW SIGNAL SQLSTATE'45000' SET MESSAGE_TEXT = 'You cant Delete Student Grade';

Query OK, 0 rows affected (0.008 sec)


MariaDB [uni]> DELETE FROM grade WHERE id=1;

ERROR 1146 (42S02): Table 'uni.grade' doesn't exist

MariaDB [uni]> DELETE FROM grades WHERE id=1;

ERROR 1644 (45000): You cant Delete Student Grade

MariaDB [uni]>

MariaDB [uni]>

MariaDB [uni]> CREATE TABLE deleted_grade(

    -> id INT PRIMARY KEY AUTO_INCREMENT,

    -> student_id INT,

    -> course_id INT,

    -> grade VARCHAR(100),

    -> deleted_at DATETIME

    -> );

Query OK, 0 rows affected (0.022 sec)


MariaDB [uni]> CREATE TRIGGER deleted_grade AFTER DELETE ON grades

    -> FOR EACH ROW INSERT INTO deleted_grade(student_id,course_id,grade,deleted_at)

    -> VALUES (OLD.student_id,OLD.course_id,OLD.grades,NOW());

Query OK, 0 rows affected (0.014 sec)


MariaDB [uni]> DROP TRIGGER delete_grades;

Query OK, 0 rows affected (0.016 sec)


MariaDB [uni]> DELETE FROM grade WHERE id=1;

ERROR 1146 (42S02): Table 'uni.grade' doesn't exist

MariaDB [uni]> DELETE FROM grades WHERE id=1;

Query OK, 1 row affected (0.004 sec)


MariaDB [uni]> select * FROM deleted_grade;

+----+------------+-----------+-------+---------------------+

| id | student_id | course_id | grade | deleted_at          |

+----+------------+-----------+-------+---------------------+

|  1 |          1 |         2 | C     | 2025-12-01 23:04:07 |

+----+------------+-----------+-------+---------------------+

1 row in set (0.001 sec)


Share:

0 comments:

Post a Comment

Contact Us

SALEHE NJOHOLE P.O.BOX 2428, DAR ES SALAAM, TANZANIA EAST AFRIKA. Call: 0692 127 931
  • ICT OFFICER II – SCANNER QUESTIONS AND ANSWERS
                MCQs for ICT OFFICER II – SCANNER Section A: Scanner Operations & Imaging Which of the following best describes t...
  • ICT OFFICER - SECURITY MANAGEMENT PRACTICAL
    1.      You are given a web application which uses MERN stack to perform a vulnerability scan.   i.           State the discovered vul...
  • ICT OFFICER II - SCANNER NOTES
        CHAPTER ONE INTRODUCTION As an ICT Officer, managing scanners involves more than basic troubleshooting; it includes administration, ...
  • SYSTEM ADMINISTRATOR QUESTIONS AND ANSWER
      1. What is Linux primarily known as? a) A web server b) An operating system c) A programming language d) A hardware platform View A...
  • MCQS on SYSTEM ADMINISTRATOR
    1.     Which language is used to write Ansible playbooks? a) JSON b) YAML c) XML d) Python   Answer: b) YAML   2.     What is the alternativ...
  • HELPDESK QUESTIONS AND ANSWERS
      Helpdesk Questions and Answers Section A: General Helpdesk Fundamentals Q1. Which of the following best describes the main role of a ...
  • PROGRAMMER QUESTIONS AND ANSWERS
      1. What is the base of a recursive function? A. The first call to the function B. The condition that stops recursion ✅ C. The infinite ...
  • PROGRAMMING PRACTICAL QUESTIONS
      PROGRAMMING PRACTICAL QUESTION        As an application developer, you are assigned by the Public Service Recruitment Secretariat to devel...
  • System Developer Questions and Answers
     System Developer QUESTION NO 1. 🔧 Tools & Definitions **What does "CI" stand for in DevOps?** A) Continuou...
  • BUSINESS ANALYST MCQS
      BUSINESS ANALYST QUESTIONS AND ANSWERS Section A: Requirements & Elicitation Q1. Which elicitation technique is best when stakeho...