MySQL Trigger Example Part 2

MySQL Trigger Example Part 2

We need a database that records customers, products and the orders taken by a online store, we will have name and contact details in customers table, in the products table we need to know the title of the product, quantity in stock and the price of the product. A orders table will  will record who purchased the product and when.



CREATE DATABASE online_store;
CONNECT online_store;

CREATE TABLE customers
(
 custid INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (custid),
 firstname VARCHAR(15),
 lastname VARCHAR(15),
 email VARCHAR(20)
);


CREATE TABLE products
(
 prodid INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(prodid),
 title VARCHAR(15),
 quantity INT,
 price DECIMAL(10,2)
);


CREATE TABLE orders
(
 ordid INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY(ordid),
 placed TIMESTAMP,
 ocustid INT,
 oprodid INT,
 FOREIGN KEY (ocustid) REFERENCES customers(custid),
 FOREIGN KEY (oprodid) REFERENCES products(prodid)
);


Now we need to populate the database with some fictitious data...




INSERT INTO Customers (FirstName,LastName,email) VALUES ('Mark','Dodds','mdodds@hotmail.com');
INSERT INTO Customers (FirstName,LastName,email) VALUES ('Paul','Clarkson','Pdodds@hotmail.com');
INSERT INTO Customers (FirstName,LastName,email) VALUES ('Jimmy','Paulson','JPaulson@hotmail.com');
INSERT INTO Customers (FirstName,LastName,email) VALUES ('June','Wilds','AJames@hotmail.com');
INSERT INTO Customers (FirstName,LastName,email) VALUES ('Danny','Armstrong','AJames@hotmail.com');
INSERT INTO Customers (FirstName,LastName,email) VALUES ('Andy','Williams','AJames@hotmail.com');

INSERT INTO Products (Title,quantity,price) VALUES ("Shoes",23,4.99);
INSERT INTO Products (Title,quantity,price) VALUES ("Jackets",34,16.99);
INSERT INTO Products (Title,quantity,price) VALUES ("Shampoo",45,1.99);

INSERT INTO Orders (OCustID,OProdID) VALUES (1,1);
INSERT INTO Orders (OCustID,OProdID) VALUES (1,2);
INSERT INTO Orders (OCustID,OProdID) VALUES (2,3);


What we want to know is which customer has purchased the shoes, this can be found with a simple SELECT statement.


SELECT  customers.firstname,customers.lastname
FROM Orders,Customers
WHERE customers.custid=orders.ocustid
AND orders.OProdID=1;



Now we have three tables - customers - products - orders, the problem we have is that the if we place a order for shoes in the orders table it will not effect the quantity in the products table, do resolve this issue we can use a trigger.


DELIMITER $$
CREATE TRIGGER update_products AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    SET @update = new.Oprodid;
    UPDATE products SET quantity=quantity-1 WHERE prodid=@update;
 
END$$
DELIMITER ;


Lets give this a try...

SELECT * FROM PRODUCTS;



+--------+-----------------+----------+-----
| ProdID | title                       | quantity | price |
+--------+-----------------+----------+-----
|      1      | Shoes                    |       23 |  4.99  |
|      2      | Jackets                  |       34 | 16.99  |
|      3      | Shampoo               |       45 |  1.99   |
|      4      | Windows Cleaner   |       78 |  2.49   |
|      5      | Car Magazine         |       71 |  1.49  |
|      6      | Eggs                       |      177 |  1.42 |
|      7      | Tea bags                 |      237 |  1.29 |
|      8     | Bread                      |       12 |  0.99  |
+--------+-----------------+----------+--------



Now we insert some more data in to the orders table..

INSERT INTO orders (OCustID,OProdID) VALUES (1,1);

And now check the table again.

SELECT * FROM PRODUCTS;


+--------+-----------------+----------+-----
| ProdID | title                       | quantity | price |
+--------+-----------------+----------+-----
|      1      | Shoes                    |       22 |  4.99  |
|      2      | Jackets                  |       34 | 16.99  |
|      3      | Shampoo               |       45 |  1.99   |
|      4      | Windows Cleaner   |       78 |  2.49   |
|      5      | Car Magazine         |       71 |  1.49  |
|      6      | Eggs                       |      177 |  1.42 |
|      7      | Tea bags                 |      237 |  1.29 |
|      8     | Bread                      |       12 |  0.99  |
+--------+-----------------+----------+--------


The table above has removed a item from the shoes column.