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:

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.

FAQs on MySQL Create Trigger

What are the types of triggers available in MySQL?

MySQL supports three types of triggers: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE. These specify whether the trigger should fire before or after an insert, update, or delete operation.

Can a trigger call another trigger in MySQL?

No, MySQL does not allow triggers to directly call other triggers. However, a trigger can indirectly affect other triggers by performing operations that cause other triggers to fire.

Can a trigger modify the data being inserted or updated?

Yes, a BEFORE INSERT or BEFORE UPDATE trigger can modify the data being inserted or updated. Changes made to NEW values in these triggers will be applied to the data before it is committed to the table.

What happens if a trigger raises an error in MySQL?

If a trigger raises an error, the operation that caused the trigger to fire will be rolled back, and the database will be restored to its state before the operation. The error message generated by the trigger will be returned to the client.