MySQL Create Trigger (original) (raw)

Last Updated : 29 Jul, 2024

**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 MySQLis 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:

Output

Example: 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:

Output

**Step 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:

Output

Conclusion

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.