Triggers in MySQL: How to Take Control of Each Type Like A Pro 

Triggers in MySQL have a domino effect. Once they set off, a series of database actions may take effect. It’s like the 2008 Financial Crisis. Shockwave after shockwave, it trampled the global economy. From US housing to banks to businesses and finally, to individuals, the effects made it to the history books. 

Triggers in MySQL are like that – a chain reaction, a domino effect. It will be a database crisis if you are not careful. Gain full command over it, and it will transform into your greatest asset.  This article will discuss the syntax, the types, their limitations, and code samples. 

What are Triggers in MySQL? 

MySQL triggers are database objects that run actions or tasks when an event in a table happens. It’s automatic. You can set one or more MySQL triggers on one or more tables. 

The basic MySQL trigger has 3 important parts: 

• Event: Only 3 events are available: INSERT, UPDATE, or DELETE operations that occur on a table. 
• Timing: It tells when the trigger will run, either BEFORE or AFTER the INSERT, UPDATE, or DELETE. 
• Action: The SQL statements you want to run when the event occurs. 

Triggers can be used for data validation, audit trails, archiving, enforcing business rules, and anything applicable. You can either CREATE a trigger for a table or DROP it when you no longer need it. 

MySQL Trigger Syntax 

Triggers in MySQL need 2 commands, one to create and another to delete. Keep in mind that an ALTER TRIGGER command does not exist. If you need to change an existing trigger, you drop it first and then, recreate it. Let’s begin. 

CREATE TRIGGER 

You can apply triggers in MySQL through database tables. CREATE TRIGGER is the command to make one. Check out the syntax below: 

CREATE 
    [DEFINER = user] 
TRIGGER [IF NOT EXISTS] trigger_name 
BEFORE | AFTER  INSERT | UPDATE | DELETE 
ON table_name FOR EACH ROW 
FOLLOWS | PRECEDES other_trigger_name 
   trigger_body 

Let’s explain each piece of the syntax: 

• CREATE – the command to make a database object. In this instance, a fresh trigger. 
• [DEFINER = user] – (optional) This defaults to the user who will create the trigger. It can be set to any other valid MySQL account on your server. Note that a trigger uses the definer’s security context. So, if the definer (or creator of the trigger) has very high privileges, it will take effect no matter what security privileges the invoker of the event has. 
• TRIGGER [IF NOT EXISTS] trigger_name – Here you specify a unique name for your trigger. The optional IF NOT EXISTS prevents an error if the trigger name already exists in the current schema. 
• BEFORE | AFTER – Pick only one timing. It’s either BEFORE or AFTER. You can have both in one table. 
• INSERT | UPDATE | DELETE – Pick only one event the trigger will run. Note that you can have more than one trigger in a table each for a different event. 
• ON table_name – Specify the name of the table you are creating the trigger for. 
• FOR EACH ROW – Tells MySQL to run the trigger once for a table row. This indicates the conclusion of the trigger’s declaration and the commencement of its execution logic. 
• FOLLOWS | PRECEDES other_trigger_name – (optional) tells MySQL if there’s another trigger that will run before your new trigger (FOLLOWS) or if your trigger should run before other_trigger_name (PRECEDES). 
• trigger_body – this is a series of SQL statements you want to run BEFORE or AFTER the event. 

DROP TRIGGER 

DROP TRIGGER deletes the trigger from the current database. 

The structure is simple to track:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name 

Use IF EXISTS to prevent an error from occurring for a non-existent trigger. 

Types of MySQL Triggers 

Now that you know what triggers are, what it is for, and the syntax, it’s time to know the two major types based on timing: the BEFORE and AFTER triggers. Let’s expand these further. 

BEFORE Triggers 

The BEFORE timing means you want the trigger action to happen first before the actual insert, update, or delete of a row occurs. Check the following diagram for the workflow of events and actions for this timing. 

This approach is ideal for executing logic immediately prior to the underlying table transaction like validating data. You can still reject the changes if the data is invalid. 

Consider a CREATE TRIGGER example using the BEFORE INSERT timing below: 

— create the customer table first 

CREATE TABLE sales.customer ( 

  customer_id int NOT NULL AUTO_INCREMENT, 

  customer_name varchar(70) NOT NULL, 

  address varchar(300) DEFAULT NULL, 

  mobile_number varchar(20) DEFAULT NULL, 

  email varchar(50) DEFAULT NULL, 

  modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 

  PRIMARY KEY (customer_id) 

); 

— Then create the trigger 

CREATE TRIGGER sales.before_new_customer_trigger 

BEFORE INSERT 

ON sales.customer 

FOR EACH ROW 

BEGIN 

 — Check if customer_name is NULL 

IF NEW.customer_name IS NULL THEN 

     — Signal an error 

     SIGNAL SQLSTATE ‘45000’ 

     SET MESSAGE_TEXT = ‘Customer name cannot be NULL’; 

ELSE 

     — Convert customer_name to uppercase 

     SET NEW.customer_name = UPPER(NEW.customer_name); 

END IF; 

END; 

It started by creating the customer table to set the stage for the trigger. Then, the trigger is created to validate the customer name. If it’s null, it raises an error. It changes the name to uppercase if it’s not. 

Let’s explain a few things: 

• CREATE TRIGGER – this creates a trigger named before_new_customer_trigger on the sales database. 
• BEFORE INSERT – this means the trigger will run before saving the new row in the table. 
• ON sales.customer – this is the name of the table this trigger is for. 
• FOR EACH ROW signals the start of the trigger body which defines the actions or the statements to run. 
• NEW.customer_name – you can refer to the row that is about to be inserted as NEW. Then, use a dot followed by any column name you wish to use in code. In this case, its customer_name. 
• SIGNAL SQLSTATE ‘45000’ – raises an error. The SET MESSAGE_TEXT defines the friendly message you want. Make sure to trap this in your application code. 
• SET NEW.customer_name = UPPER(NEW.customer_name)– Sets the value of the non-null customer name to upper case. 

AFTER Triggers 

AFTER triggers mean that the trigger actions will run after committing the insert, update, or delete on a table. Refer to the subsequent flowchart to visualize the operational sequence:

This is good for archiving or logging changes for a row in a table. The log or archive is useful for tracking changes and troubleshooting. 

Check out an example below: 

— Create the customer log table first 

CREATE TABLE sales.customer_log ( 

    log_id INT AUTO_INCREMENT PRIMARY KEY, 

    customer_id INT NOT NULL, 

    old_name VARCHAR(70), 

    new_name VARCHAR(70), 

    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP 

); 

— Then, create the trigger 

CREATE TRIGGER after_customer_update 

AFTER UPDATE ON sales.customer 

FOR EACH ROW 

BEGIN 

— Check if the customer_name has changed 

IF OLD.customer_name != NEW.customer_name THEN 

     — Log the change into a logging table 

     INSERT INTO sales.customer_log (customer_id, old_name, new_name, change_time) 

     VALUES (NEW.customer_id, OLD.customer_name, NEW.customer_name, NOW()); 

END IF; 

END; 

The above code will log the before and after values in another table sales.customer_log after a successful update of the sales.customer table. 

Let’s dig further: 

• CREATE TRIGGER after_customer_update – the name of the new trigger to create. 
• AFTER UPDATE ON sales.customer – tells you that the new trigger is for an UPDATE command for the customer table. The trigger action will run after a successful UPDATE. 
• IF OLD.customer_name != NEW.customer_name – You already knew about the NEW keyword from the previous example. The OLD keyword refers to previous row values before the update. It compares the old customer name to the new customer name. 
• INSERT INTO sales.customer_log – insert the before and after values of the customer name in the customer_log table. 

This is a simple implementation to log the values of the customer name. Ideally, logging every change can help in audits and backtracking when there’s a problem related to the customer record. 

Also Read: VS Code vs VSCodium: Real Differences in the Open Source?

Supported Trigger Events in MySQL 

We already mentioned that MySQL supports events for INSERT, UPDATE, and DELETE statements. Each of these events can have their own BEFORE and AFTER triggers. A table can also have triggers for all these events. 

Let’s explain this further in this section with code samples. 

INSERT Triggers 

INSERT triggers are for INSERT statements. Whenever there’s a new row in a table, the INSERT trigger will run. You already saw a BEFORE INSERT earlier. 

Below is an AFTER INSERT example to update a data warehouse table. 

CREATE TRIGGER after_customer_insert 

AFTER INSERT ON sales.customer 

FOR EACH ROW 

BEGIN 

— Execute a data insertion for a new client entry into the customer dimension table

INSERT INTO sales_dw.dim_customer (customer_id, customer_name, change_time) 

VALUES (NEW.customer_id, NEW.customer_name, NOW()); 

END 

When it makes sense, MySQL triggers can be good at syncing transactional databases to data warehouses. 

UPDATE Triggers 

Your guess for what UPDATE triggers are for is as good as mine. We already had an AFTER UPDATE trigger example earlier. 

Here’s another example using BEFORE UPDATE: 

— Create the product table first 

CREATE TABLE sales.product ( 

  product_id int NOT NULL AUTO_INCREMENT, 

  product_name varchar(30) NOT NULL, 

  purchase_price decimal(10, 2) NOT NULL, 

  selling_price decimal(10, 2) NOT NULL, 

  created timestamp NULL DEFAULT CURRENT_TIMESTAMP, 

  PRIMARY KEY (product_id) 

); 

— Then, create the trigger 

CREATE TRIGGER before_product_update  

BEFORE UPDATE ON sales.product 

FOR EACH ROW 

BEGIN 

  IF NEW.selling_price < NEW.purchase_price THEN 

SIGNAL SQLSTATE ‘45000’  

SET MESSAGE_TEXT = ‘Selling price cannot be lower than purchase price.’; 

  END IF; 

END; 

The above code is a validation of the selling price of a product. The value must remain at or above the original acquisition cost.

You are already familiar with the NEW, SIGNAL SQLSTATE, and SET MESSAGE_TEXT as this is the same from the BEFORE INSERT example. Aside from the specific identifiers for the table, columns, and trigger, the only structural variation is the use of the BEFORE UPDATE event.

DELETE Triggers 

DELETE triggers are as the name implies. It’s for the DELETE statement. 

Here’s an example using the product table earlier: 

CREATE TRIGGER before_product_delete  

BEFORE DELETE ON sales.product 

FOR EACH ROW 

BEGIN 

  IF OLD.purchase_price < 100.00 THEN 

SIGNAL SQLSTATE ‘45000’  

SET MESSAGE_TEXT = ‘Cannot delete products with purchase price below 100.00.’; 

  END IF; 

END; 

This is a BEFORE DELETE trigger that will not allow deletion of products with purchase price below 100.00. Notice that the OLD keyword applies here. NEW will not make sense. 

Here’s an AFTER DELETE with the archiving usage scenario: 

— First, create the table to store deleted product details 

CREATE TABLE sales.deleted_products ( 

  product_id int, 

  product_name varchar(30), 

  purchase_price decimal(10, 2), 

  selling_price decimal(10, 2), 

  deleted_at timestamp NULL DEFAULT CURRENT_TIMESTAMP 

); 

— Now, create the AFTER DELETE trigger 

CREATE TRIGGER after_product_delete  

AFTER DELETE ON sales.product 

FOR EACH ROW 

BEGIN 

  INSERT INTO sales.deleted_products (product_id, product_name, purchase_price, selling_price) 

  VALUES (OLD.product_id, OLD.product_name, OLD.purchase_price, OLD.selling_price); 

END; 

Once the deletion is successful, an archive table named deleted_products will store the deleted row values using the OLD keyword. Again, NEW is not applicable. 

How to CREATE a Trigger in MySQL 

For this article, I’m going to use dbForge Studio for MySQL as MySQL IDE. and also, the MySQL version is 9.1.0. 

One way to create a trigger in dbForge Studio is from the Triggers tab of the Table Designer. 

Consider the steps below. 

5 Steps to Create Triggers in MySQL Using dbForge Studio for MySQL 

dbForge Studio for MySQL makes it easier to create triggers in MySQL. It lets you bypass typing the boilerplate code and focus on the essentials. It also minimizes syntax errors. 

Assuming dbForge Studio for MySQL is open, the following are the steps below: 

STEP 1: Choose the Table 

From the Database Explorer, right-click the table you wish to create the trigger for. See a sample screenshot below:

This will open the Table Designer. The initial view you see is the Columns tab. You will see the table columns and their types here.

STEP 2: Click the Triggers Tab and Create the Trigger 

There are several tabs in the Table Designer. For this step, click the Triggers tab. You will see a blank space if the table has no triggers yet. To initiate a new trigger, you can either right-click within the empty area and choose ‘New Trigger’ or use the INS keyboard shortcut.

See a sample below: 

This will open another window for creating a trigger. 

STEP 3: Enter the Trigger Details in the Form 

Once the trigger editor appears, enter the Name, Timing, and Event. Optionally, set the Definer and the Order. See a sample below: 

Whenever you change the values in each of the boxes, the code below it changes too. You can choose to change the code below. It will use intelligent SQL coding to give you code completion and syntax checking. It’s your choice. 

STEP 4: Provide the Trigger Body or Action 

While the specific implementation depends on your preferences, the system’s smart coding capabilities will remain active and available. So, it will help you avoid syntax errors and non-existent database objects. 

Check out the sample below with the complete setup:
 

Once finished, select ‘Apply Changes‘ to commit the newly created trigger to the database. Then, you can close the trigger editor. 

You can now see one entry in the list of triggers for the table. See a sample below: 

STEP 5: Test the Trigger 

Let’s see if the trigger will work by updating one product. Remember the BEFORE UPDATE trigger forbids a selling price less than the purchase price. Our sample has 466.56 purchase price. We will use an UPDATE statement to set the selling price to 1. This should trigger the error. 

See the result below in dbForge Studio for MySQL. It uses 2 SQL windows with a horizontal arrangement to see the comparison. 

See? The BEFORE UPDATE trigger works! 

That’s it! Five steps to create a new MySQL trigger in dbForge Studio for MySQL. 

Debugging Triggers in MySQL 

Old-school SQL debugging means isolating code portions, manually setting values, and running them. When it’s good, proceed to the next statement until everything is traced. This is harder with MySQL triggers. 

dbForge Studio for MySQL offers a debugger for your MySQL code, including triggers, procedures, functions, and SQL scripts. But debugging a trigger requires the following: 

• A test database to isolate debugging from the development database. This will avoid messing up the data in case you make a mistake. 
• Deploying the debug engine to the server. This is a 1-click action accessible through the main menu. 
• Compiling the trigger with debug info. 
• The table event should originate from a stored procedure or line tracing from procedure to trigger will not work. Breakpoints will also be ineffective. The procedure also needs to be compiled for debugging. 

This is better than old-school MySQL debugging. 

5 Steps to Debug a MySQL Trigger 

1. Deploy the debug engine. From the main menu, click Debug ->Deploy Debug Engine. 
2. Compile the trigger for debugging. From the Database Explorer, right-click the trigger and select Compile -> Compile for Debugging. 
3. Add your desired breakpoint to the trigger. If you have experience debugging in Visual Studio or Visual Studio Code, it has the same look and feel. Click the line of code or press F9. 
4. Add a breakpoint in the stored procedure, if necessary. This should be for an INSERT, UPDATE, or DELETE to run the trigger you are debugging. 
5. Press CTRL-F5 to run the procedure or F11 to trace line-by-line until you’re back in the stored procedure from the trigger, or you reach the end of your procedure code. 

Check out the image below with a breakpoint in the trigger: 

Below is the preparation for running the procedure by providing parameter values: 

And below is a tracing from the stored procedure. It shows where the debugger is (yellow line with a pointer). 

After the UPDATE finishes, the debugger will jump to the trigger. You can also run until the breakpoint in the trigger is reached. 

Limitations in MySQL Triggers 

There are certain limitations about MySQL triggers that you should be aware of: 

• Though there is a DELETE trigger, it won’t run when you use TRUNCATE. 
• Dropping a table drops all associated triggers in it. 
• You can use both OLD and NEW keywords in UPDATE triggers to reference columns from the old and new values. The OLD keyword doesn’t apply to INSERT triggers, much like NEW doesn’t apply to DELETE triggers. 
• Columns referred from the OLD keyword are read-only. 
• An AFTER trigger will only activate if both the BEFORE trigger and the table event succeed. 
• If a trigger causes an error, the entire statement or event that runs the trigger will also result in failure. 
• A trigger runs synchronously. The statement or event that invokes the trigger won’t be complete until the trigger succeeds. 
• In a statement or event in a transaction, the failure of the trigger will cause a rollback. 
• Cascaded foreign key actions do not activate triggers. 
• Use triggers with care. Do not use on tables with a high volume of inserts and updates. It will be a performance overhead. 

Making the Most of MySQL Triggers 

In summary, we discussed what are triggers in MySQL, their types based on timing and events, syntax, debugging, and limitations. 

Why not explore the powers of MySQL triggers in automating tasks, like logging and archiving? You can use a MySQL Swiss army knife, dbForge Studio, the ultimate all-in-one MySQL GUI. With this, you can quickly create triggers and other database objects. You can download a copy from the official website. Then, explore the features like intelligent SQL coding, database designer, Query Profiler, and more.