Like me, you are also a MySQL user. Congratulations, you have chosen the world’s best and most popular open-source database. Now, there is no need to worry about your programming language or framework because MySQL offers application developers everything they need to build database-driven information systems. Today, I will discuss a comprehensive knowledge of MySQL triggers, also known as SQL triggers. You can read this article if you are tech-savvy and want detailed information about MySQL triggers.
What is MySQL Trigger?
MySQL Triggers are the database objects or stored programs invoked automatically when a defined action such as INSERT, UPDATE, or DELETE is executed for an associated database table. So, if you want to run database triggers on MySQL, you need SUPERUSER privileges. If you are hosting your website on a shared hosting environment, then you can’t run any trigger MySQL.
It would be best to have a VPS (Virtual Private Server) Or Dedicated Server to get SUPERUSER rights to run triggers in MySQL.
Mainly there are two types of SQL triggers: row-level triggers and statement-level triggers.
Row-Level trigger: It is activated or executed whenever you need to insert, update, or delete an event for each row. For example, if you want to delete 50 rows, then the database trigger should be invoked automatically 50 times for the 50 rows affected.
Statement-level Trigger: As the name indicates, it is executed once for each database transaction instead of how many rows are updated, deleted, or inserted.
Also Read: SQL vs MySQL: Making the Right Database Choice For You
MySQL doesn’t support statement-level SQL triggers and only executes the row-level triggers.
How to Create a MySQL Trigger?
CREATE TRIGGER [schema_name.]trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
{sql_statements}
What do you have to do? Define a set of SQL statements that you want to be executed when the trigger is fired. Now specify when the trigger will be executed, and it is set by the {FOR | AFTER | INSTEAD OF} section. INSERT, UPDATE, and DELETE options denote which SQL commands fire this trigger.
If you want to drop or delete a MySQL trigger, then run the following command:
DROP TRIGGER [schema_name.]trigger_name;
MySQL Trigger Example:
- Open SSH and create a database table so that we can execute a trigger on it.
mysql> CREATE TABLE student (age INT, name varchar(150));
- Now, define a trigger, which will be executed before every insert statement for the student table.
mysql> delimiter // mysql> CREATE TRIGGER agecheck BEFORE INSERT ON student FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;
- Insert three records to confirm the trigger functionality.
mysql> INSERT INTO student VALUES (-10, 'david'), (20, 'sara'), (30, 'viktor');
- Here is the output.
mysql> SELECT * FROM student;
+——-+——-+ | age | name | +——-+——-+ | 0 | David | | 20 | Sara | | 30 | Viktor | +——-+——-+