MYSQL After Insert Trigger
MYSQL
A MySQL AFTER INSERT trigger is a stored program used to automatically execute a block of SQL code immediately after a new row is successfully inserted into a specific table.
MySQL After Insert Trigger
➔ This trigger can be used to automatically record details of new entries in a separate log or audit table.
➔ It is typically used for change logging, updating related tables, or enforcing complex business rules.
➔ The DELIMITER command in MySQL is primarily used to redefine the character that signals the end of an SQL statement in the client-side interface. The default DELIMITER is semicolon(;).
Problem definition:
Problem: Create and apply a trigger to log when there are new entries in the table.
Create table:
Example
/* Create table */
CREATE TABLE demo_users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
deptId INT NOT NULL
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
/* Insert values */
INSERT INTO demo_users (username, deptId)
VALUES ("John Smith", 101);
Copy the code and try it out in your learning environment.
Create audit table:
Example
/* Create table */
CREATE TABLE demo_audit_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action_type VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Copy the code and try it out practically in your learning environment.
Describe table structure:
Example
/* Check table structure */
DESCRIBE demo_users;
Copy the code and try it out practically in your learning environment.
Table structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | auto_increment | |
| username | varchar(255) | NO | |||
| deptId | int | NO |
Query value:
Example
/* Query data */
SELECT * FROM demo_users;
Copy the code and try it out practically in your learning environment.
Table Data:
| id | username | deptId |
|---|---|---|
| 1 | John Smith | 101 |
Create Trigger:
Solve: Create and apply a trigger to log when there are new entries in the table.
Example
/* Change the delimiter from the default (;) to $$ */
DELIMITER $$
CREATE TRIGGER trigger_after_insert_demo_users
AFTER INSERT ON demo_users
FOR EACH ROW
BEGIN
INSERT INTO demo_audit_log (user_id, action_type)
VALUES (NEW.id, 'User Added');
END$$
/* Reset the delimiter back to the default semicolon (;) */
DELIMITER ;
Copy the code and try it out practically in your learning environment.
Insert new data into the data table:
Example
/* Insert values */
INSERT INTO demo_users (username, deptId)
VALUES ("Mary Johnson", 102);
Copy the code and try it out in your learning environment.
Query data table:
Example
/* Query data */
SELECT * FROM demo_users;
Copy the code and try it out practically in your learning environment.
Table Data:
| id | username | deptId |
|---|---|---|
| 1 | John Smith | 101 |
| 2 | Mary Johnson | 102 |
Query log/audit table:
Example
SELECT * FROM demo_audit_log;
Copy the code and try it out practically in your learning environment.
Drop Trigger:
Example
/* Drop a trigger */
DROP TRIGGER IF EXISTS trigger_after_insert_demo_users;
Copy the code and try it out practically in your learning environment.