Database triggers are specialized procedures that automatically respond to certain events on a table or view. These events include actions such as INSERT, UPDATE, or DELETE. Triggers can be used to enforce complex business rules, maintain audit trails, or synchronize data across tables. In MySQL, triggers are useful concepts for automating and making sure of the integrity of database operations.
MySQL Create Trigger
The CREATE TRIGGER statement in MySQL is used to create a new trigger in the database. It specifies the event (INSERT, UPDATE, or DELETE) and the timing (BEFORE or AFTER) of the trigger's execution. When the specified event occurs, the trigger automatically executes the defined SQL statements, allowing for automated actions and data integrity enforcement.
Syntax:
The syntax for using Create Trigger in MySQL is as follows:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
-- SQL statements
END;
Parameters
trigger_name: The name of the trigger.{BEFORE | AFTER}: Specifies whether the trigger action should happen before or after the triggering event.{INSERT | UPDATE | DELETE}: Specifies the event that will activate the trigger.table_name: The name of the table to which the trigger is attached.FOR EACH ROW: Specifies that the trigger will be executed once for each row affected by the triggering event.BEGIN ... END: The block of SQL statements to be executed when the trigger is activated.
MySQL Create Trigger Example
Let’s look at some examples of the Create Trigger in MySQL. Learning the Create Trigger with examples will help in understanding the concept better.
First, let’s create a table:
Demo MySQL Tables
We create the table "students" in this example.
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
grade CHAR(1)
);
DESCRIBE students;
Output:
OutputExample: Creating Trigger that Performs Logging Insert Operations
In this example, we create a trigger named after_student_insert that activates after an INSERT operation on the students table. It inserts a record into the students_log table, logging the student_id and the action 'Inserted' each time a new student is added.
Step 1: Firstly we need to create a log table to track the insert operations.
CREATE TABLE students_log (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
action VARCHAR(50)
);
Output:
OutputStep 2: Now, we will create a trigger to log insert operation.
DELIMITER //
CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO students_log (student_id, action)
VALUES (NEW.id, 'Inserted');
END//
DELIMITER ;
Step 3: Insert Data and Verify Log
Insert a new record into the students table and then check the students_log table to see if the log entry was created:
INSERT INTO students (name, grade) VALUES ('GFG', 'B');
SELECT * FROM students_log;Output:
OutputConclusion
In Conclusion, MySQL triggers are powerful tools that automate actions in response to specific events on database tables. By defining triggers with the CREATE TRIGGER statement, you can enforce business rules, maintain logs, and ensure data integrity automatically. Understanding and using triggers effectively can greatly enhance the functionality and reliability of your database operations.
Similar Reads
MySQL DROP Trigger
In MySQL, triggers automatically perform actions when events like INSERT, UPDATE, or DELETE occur on a table However, there are situations where a trigger may not be necessary and its logic may need to be updated. In such cases, MySQL provides the DROP TRIGGER statement to delete an existing trigger
4 min read
MySQL CREATE TABLE
Creating tables in MySQL is a fundamental task for organizing and managing data within a database. Tables act as structured containers, similar to spreadsheets, where data is stored in rows and columns. In this article, we will explore the process of creating tables in MySQL using both the Command L
4 min read
MySQL AFTER DELETE Trigger
In MySQL, triggers are a concept where SQL developers can define automatic actions to occur in response to specific changes in a database table. An AFTER DELETE trigger, for instance, is invoked automatically after a row is deleted from a table, allowing developers to perform additional operations s
4 min read
MySQL BEFORE DELETE Trigger
MySQL BEFORE DELETE trigger is a powerful tool that automatically performs specific actions before an DELETE operation is executed on a table. This feature allows us to handle tasks such as logging deleted records, enforcing business rules or validating data before it is removed from the database. I
3 min read
MariaDB Create Triggers
Triggers are a very useful and powerful feature of MariaDB. It is a database object associated with a table that activates if an INSERT, UPDATE or DELETE operations are performed. The name itself reflects their action as they run immediately without any human intervention when the respective operati
6 min read
MySQL AFTER UPDATE Trigger
Triggers in MySQL are special stored programs that are automatically executed when a specific event occurs on the table such as an INSERT, UPDATE, or DELETE. An AFTER UPDATE trigger is a type of trigger that executes after an UPDATE statement is performed on the table. This article provides a comple
4 min read
MySQL After Insert Trigger
An "AFTER INSERT" trigger in MySQL automatically executes specified actions after a new row is inserted into a table. It is used to perform tasks such as updating related tables, logging changes or performing calculations, ensuring immediate and consistent data processing. In this article, We will l
4 min read
MySQL Before Insert Trigger
MySQL BEFORE INSERT triggers are essential tools for maintaining data quality and enforcing business rules at the database level. These triggers automatically execute a series of SQL statements before a new row is inserted into a table, allowing for data validation, modification, and enhancement. An
5 min read
SQL CREATE TABLE
In SQL, creating a table is one of the most essential tasks for structuring your database. The CREATE TABLE statement defines the structure of the database table, specifying column names, data types, and constraints such as PRIMARY KEY, NOT NULL, and CHECK. Mastering this statement is fundamental to
5 min read
PL/SQL Enable Triggers
In PL/SQL, triggers are special stored procedures that automatically execute in response to certain events on a particular table or view. These triggers can be used to enforce complex business rules, maintain integrity constraints, or track changes in the database. However, triggers can be enabled o
6 min read