Method for Trigger and Advanced Logging in MySQL

[BackUP]

Let's delve into the detailed process of handling triggers in MySQL as a version of the advanced logging management for SQL Server that I previously discussed. In this new article, we will cover this topic thoroughly. Following the request regarding the MySQL version of Trigger and Advanced Logging Methodology in SQL Server, we have prepared a short guide to shed light on this subject for MySQL. To begin with, we will provide an overview and then proceed to the coding part by explaining its functionality using a simple example of a student school grading system. Firstly, let's create our tables.

**tblCourses Table**
```sql
CREATE TABLE tblCourses (
course_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
course_name VARCHAR(255),
year INT,
first_exam_grade INT,
second_exam_grade INT
)
```

**tblCourses Table View**
We have created our table. Next, we will construct a similar table for logging operations. However, there will be some differences as we want to also record information about the user who performs actions like insert, update, delete on the table.

**Courses Log Table**
```sql
CREATE TABLE tblCourses_Log (
course_ID INT,
course_name VARCHAR(255),
year INT,
first_exam_grade INT,
second_exam_grade INT,
log_ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
log_operation VARCHAR(30),
log_date TIMESTAMP NOT NULL DEFAULT NOW(),
log_creator VARCHAR(100),
log_ip VARCHAR(20)
)
```

Let's log the Insert operation on the tblCourses table. Before inserting a record into the tblCourses table, let's also add a copy of this record to the tblCourses_Log table.

**Creating a Before Insert Trigger on tblCourses Table**
```sql
CREATE TRIGGER trgCourses_Before_Insert BEFORE INSERT ON tblCourses FOR EACH ROW
BEGIN
DECLARE ip VARCHAR(20);
SELECT SUBSTRING_INDEX(host,':',1) INTO ip FROM information_schema.processlist WHERE ID=connection_id();
INSERT INTO tblCourses_Log (course_ID, course_name, year, first_exam_grade, second_exam_grade, log_operation, log_date, log_creator, log_ip)
VALUES (NEW.course_ID, NEW.course_name, NEW.year, NEW.first_exam_grade, NEW.second_exam_grade, 'Insert Before', NOW(), CURRENT_USER(), ip);
END
```

Let's visually share the created trigger. Now, let's insert some data into the tblCourses table to test this trigger.

**Inserting Values into tblCourses Table**
```sql
INSERT INTO tblCourses (course_name, year, first_exam_grade, second_exam_grade) VALUES ('Math', 2002, 54, 71);
INSERT INTO tblCourses (course_name, year, first_exam_grade, second_exam_grade) VALUES ('Turkish', 2002, 66, 57);
INSERT INTO tblCourses (course_name, year, first_exam_grade, second_exam_grade) VALUES ('English', 2002, 81, 87);
```

This process demonstrates the basic logging mechanism you can implement. Have log-filled days! Source: [https://www.ontedi.com/sql/mysqlde-...etikleme-ve-gelismis-log-kayit-tutma-yontemi)