Triggers in MySQL

Triggers are associated with a table and can not operate on it's own. If it's not specified to a table - it does not exist.

Types of Triggers:

1. BEFORE INSERT
2. AFTER INSERT
3. BEFORE UPDATE
4. AFTER UPDATE
5. BEFORE DELETE
6. AFTER DELETE

Trigger general flow / Syntax

In text:

CREATE TRIGGER TriggerName
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        -- trigger body   
        END;

A new terminology: DELIMITER

The default delimiter MySQL considers is semicolon (;). However, in cases where ; might be used before the actual end of the query, command - we need to find a way to get past it. This is where we set the delimiter to be anything else. Often times, it is commonly set to $$, which means that the semicolon (;) used in the trigger code WILL NOT be interpreted as the end of the statement.

Then, after the END statement of the trigger, you need to use the same delimiter ($$) again to tell MySQL that the trigger definition has ended, and reset the delimiter to its default value (;) using the statement DELIMITER ;.

An example of the same as below:

DELIMITER $$
CREATE TRIGGER TriggerName
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        -- trigger body   
        END $$
DELIMITER ;

Let's practice.

First, let's create the database:

CREATE DATABASE triggers;

Now, let's create our first table:

CREATE TABLE `employee` (
  `name` varchar(45) NOT NULL,
  `occupation` varchar(35) DEFAULT NULL,
  `working_date` date DEFAULT NULL,
  `working_hours` varchar(10) DEFAULT NULL,
  `Age` varchar(3) DEFAULT NULL
);

Dumping data for table employee:

INSERT INTO `employee` (`name`, `occupation`, `working_date`, `working_hours`, `Age`) VALUES
('Antonio', 'Business', '2020-10-04', '11', '52'),
('Brayden', 'Teacher', '2020-10-04', '12', '44'),
('Marco', 'Doctor', '2020-10-04', '14', '28'),
('Peter', 'Actor', '2020-10-04', '13', '26'),
('Robin', 'Scientist', '2020-10-04', '12', '29'),
('Warner', 'Engineer', '2020-10-04', '10', '32');

BEFORE INSERT Trigger - 1

DELIMITER $$
CREATE TRIGGER capitalize_name
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
SET NEW.name = CONCAT(UCASE(LEFT(NEW.name, 1)), SUBSTRING(NEW.name, 2));
END $$
DELIMITER ;

Where to find the triggers?

From your phpMyAdmin interface - click on database you're working under. On the page that opens, from top menu bar locate "Triggers" and click on it to find it:

Clicking on it would open interface as below where all the avaiable triggers for the database along with the table those trigger are associated with is displayed:

Ok, so let's get to the first trigger we just created - what does it do and how does it work?

Ignoring the general structure for trigger, and just taking the query from it, that would be:

SET NEW.name = CONCAT(UCASE(LEFT(NEW.name, 1)), SUBSTRING(NEW.name, 2));

Here's a breakdown of what each function does:

1. LEFT(NEW.name, 1): Returns the leftmost character of the name field, i.e. the first letter.
2. UCASE((LEFT(NEW.name, 1)): Converts the first letter from left to uppercase.
3. SUBSTRING(NEW.name, 2): Returns the remainder of the name field starting from the second character.
4. CONCAT(): Concatenates the capitalized first letter with the rest of the name field.

This will take the first character of the name and capitalize it and then join it with all the other text to insert the name.

Let's test if the trigger work by attempting to insert a name which is in lower case via SQL:

INSERT INTO `employee` (`name`, `occupation`, `working_date`, `working_hours`, `Age`) VALUES
('john', 'Business', '2020-10-04', '11', '52');

The outcome however, has the first letter 'j' of "john" capitalized even if we entered it in all lower case:

This confirms that the query did work as required.

BEFORE INSERT Trigger - 2

Let's test with another trigger for the same table.

DELIMITER $$
CREATE TRIGGER set_min_working_hours
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    IF NEW.working_hours < 8 THEN
        SET NEW.working_hours = 8;
    END IF;
END $$
DELIMITER ;

This trigger do not allow a new employee with working hours less than 8. If it's any lower it'll automatically set it to be 8.

Let's test this by inserting an employee with working hour less than 8:

INSERT INTO employee (name,working_hours) VALUES
('master',3);

The outcome:

As we can see from the above outcome - the name even if was inserted in all lower case has it's first letter capital while the working hour even if was set to be 3 is coming as 8.

This refers that both the triggers are working for the following table.

Let's create 2 tables order and counter to test AFTER INSERT trigger. Below is the code used to create the table and dump values as required. We will only populate one of the two tables as of now:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    customer_name VARCHAR(50),
    total_amount DECIMAL(10, 2)
);

CREATE TABLE counter (
    id INT PRIMARY KEY,
    order_count INT DEFAULT 0
);

INSERT INTO counter (id, order_count) VALUES (1, 0);

Let's now create a trigger to count the number of orders i.e. as any new values are inserted in the table orders - the order_count field under counter table should update as well with the correct count.

AFTER INSERT Trigger - 1

DELIMITER $$
CREATE TRIGGER orders_insert_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE counter SET order_count = order_count + 1;
END $$
DELIMITER ;

Let's try inserting the values to see how it works:

INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-21', 'John Doe', 100.00);
INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-21', 'Ram Bahadur', 300.00);

Here's the outcome:

With 2 new values inserted to order table - the order_count increased accordingly. You can test this further by adding more orders.

AFTER INSERT Trigger - 2

For this, let's try to calculate the total order value. And, for which we will create a table amount_counter

CREATE TABLE amount_counter (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00
);

INSERT INTO amount_counter (total_amount) VALUES (0);

Now, let's create a trigger for the same as below to calculate the total order value (P.S. this will only count for new orders as such if you do already have fields in the table the outcome will be different) :

DELIMITER $$
CREATE TRIGGER orders_amount_trigger 
AFTER INSERT ON orders 
FOR EACH ROW 
BEGIN
  UPDATE amount_counter SET total_amount = total_amount + NEW.total_amount WHERE id = 1;
END $$
DELIMITER ;

Let's test this by inserting two new values:

INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-22', 'Sita Kumari', 200.00);
INSERT INTO orders (order_date, customer_name, total_amount) VALUES ('2023-03-22', 'RK Bahadur', 400.00);

Now, visiting amount_counter table, we can see that the total_amount is 600 as it should be based on the two values we added:

For learning about BEFORE UPDATE trigger - let's start by first creating the table and inserting dummy data to it:

CREATE TABLE employee_salary (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);

INSERT INTO employee_salary (id, name, salary) VALUES
(1, 'John', 50000),
(2, 'Sarah', 60000),
(3, 'Mike', 45000),
(4, 'Emily', 55000);

Once the table is created - let's now create the trigger for BEFORE UPDATE action. In this trigger, we check for UPDATE action if the salary is anything lower than 40,000 and if it is the case - the salary is auto set to the lowest limit allowed which would be 40,000.

DELIMITER $$
CREATE TRIGGER update_salary
BEFORE UPDATE ON employee_salary
FOR EACH ROW
BEGIN
    IF NEW.salary < 40000 THEN
        SET NEW.salary = 40000;
    END IF;
END $$
DELIMITER ;

Let's test the trigger to action - for this let's try setting the salary for one of the employee to 35,000 and see what happens:

UPDATE employee_salary SET salary = 35000 WHERE id = 3;

We can see that the employee with id 3 has salary set to 40,000 by the system even if we had updated the salary to be 35,000 due to the trigger in place.

The trigger prevented the salary to be set any lower than 40k - anything lower and it's set to 40k automatically.

AFTER UPDATE trigger exercise

Let's create 2 tables product and product_inventory to test AFTER UPDATE trigger. Below is the code used to create the table and dump values as required:

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2),
  quantity INT
);

INSERT INTO product (id, name, price, quantity)
VALUES
  (1, 'Product 1', 10.00, 5),
  (2, 'Product 2', 20.00, 3),
  (3, 'Product 3', 30.00, 7),
  (4, 'Product 4', 15.00, 2);

CREATE TABLE product_inventory (
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (product_id)
);

INSERT INTO product_inventory (product_id, quantity)
VALUES
  (1, 5);

Now, let's create a trigger to update the product_inventory table with the updated stock for product_id 1:

DELIMITER $$
CREATE TRIGGER update_product_inventory
AFTER UPDATE ON product
FOR EACH ROW
BEGIN
  DECLARE inventory INT;

  SET inventory = NEW.quantity - OLD.quantity;

  UPDATE product_inventory
  SET quantity = quantity + inventory
  WHERE product_id = NEW.id;
END $$
DELIMITER ;

Now, to test it let's update quantity for Product 1 from 5 to 8 and see what happens:

UPDATE product
SET quantity = 8
WHERE id = 1;

The outcome as below shows that the product_inventory table does have quantity increased to 8 instead of previous 5 even if the above UPDATE command was only done for product table, confirming the query action:

BEFORE DELETE trigger exercise

For the following exercise, let's create a table products of whose structure and dummy data are as below:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock_quantity INT NOT NULL
);

INSERT INTO products (id, name, price, stock_quantity) VALUES
(1, 'Product 1', 10.50, 0),
(2, 'Product 2', 5.99, -50),
(3, 'Product 3', 20.00, 25),
(4, 'Product 4', 15.75, 75);

Now, let's create a trigger which will not allow the removal of any product if it has any stuck left (meaning - it'll only allow to remove products with 0 or lesser stock_quantity):

DELIMITER $$
CREATE TRIGGER delete_products
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
  IF OLD.stock_quantity > 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Cannot delete product, stock quantity still left.';
  END IF;
END $$
DELIMITER ;

Now, let's test the function by removing product_id 1 which has 0 as stock_quantity:

DELETE FROM products WHERE id = 1;

Outcome is successful execution of the same and data removal:

Let's now try removing another product with product_id 3 which has 25 as stock_quantity (which is higher than 0) :

DELETE FROM products WHERE id = 3;

Outcome for this is a failed process:

So, we can see the successful execution of the BEFORE DELETE trigger here which did not allow the operation to complete if there's at least one or more quantity left but did allow the removal of a product if it has 0 or lesser product left to sell.

AFTER DELETE trigger exercise

For the following activity/learning let's create two tables salaries and total_salary_budget and insert dummy data to it as below:

CREATE TABLE salaries (  
    emp_num INT PRIMARY KEY,  
    salary DEC(8 , 2 ) NOT NULL DEFAULT 0  
);

INSERT INTO salaries (emp_num, salary)  
VALUES  
    (101, 50000),  
    (102, 55000),  
    (103, 60000),
    (104, 65000),
    (105, 70000),              
    (106, 75000);

CREATE TABLE total_salary_budget(  
    total_budget DECIMAL(10,2) NOT NULL  
);

INSERT INTO total_salary_budget (total_budget)  
SELECT SUM(salary) FROM salaries;

Now, let's create a trigger to update total_salary_budget table's total_budget field with the updated budget as a employee is fired/leaves the job (here: removed from the database table):

DELIMITER $$    
CREATE TRIGGER after_delete_total_salary  
AFTER DELETE  
ON salaries FOR EACH ROW  
BEGIN  
   UPDATE total_salary_budget SET total_budget = total_budget - old.salary;  
END $$   
DELIMITER ;

Before writing a query to remove an employee - let's take a minute to review the current total_budget where we can see it's 375,000.00:

Now, let's remove emp_num from salaries table using the following command and see what happens:

DELETE FROM salaries WHERE emp_num = 101;

emp_num 101 had a salary of 50,000 as such when the employ is removed from the salary slab the total_budget which was previously 375,000 should come as 325,000 if it was successfully updated. Let's check:

We can indeed see that the total_budget is now 325,000 and as such we can confirm that the trigger did function as was intended.

This is all for Triggers in MySQL. If you have any further concerns or questions - do drop them below in the comments section.