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:
- BEFORE INSERT
- AFTER INSERT
- BEFORE UPDATE
- AFTER UPDATE
- BEFORE DELETE
- AFTER DELETE
Trigger general flow / Syntax
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 ;
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
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:
- LEFT(NEW.name, 1): Returns the leftmost character of the name field, i.e. the first letter.
- UCASE((LEFT(NEW.name, 1)): Converts the first letter from left to uppercase.
- SUBSTRING(NEW.name, 2): Returns the remainder of the name field starting from the second character.
- 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);
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
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
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);
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_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
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
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_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
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.
Top comments (0)