A Trigger Example in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 5

Division 5

Forward: In this part of the series, we look at an example of a trigger in MySQL.

By: Chrysanthus Date Published: 6 Aug 2012

Introduction

This is part 5 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. You must have read all the different parts of the series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at an example of a trigger in MySQL.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Example
The Products table for the wholesale database is:

Products(ProductID, ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice)

Each row in the Products table has information about one product in the company. The value of each ReorderLevel cell in a row is the minimum number of that product that should be in stock. As the company is selling, the value in the Quantity column for each row is reducing. As soon as the value in the Quantity column cell reaches or goes below the value in the ReorderLevel column cell of the same row, that situation is interpreted as an event. It means the CEO (or highest manager) has to be informed so that he arranges for a new set of the same product to be ordered.

Now, you can have another table in the database that the highest manager looks at everyday at say 12 O’clock. When that event occurs, the event handler code will place a new row in this table, to indicate the time the event occurred, product concerned, event message, the reorder level and the current level (number of products). When the highest manager sees such new row information in his table, it is up to him to take a decision (arrange for reordering).

We shall also create a new table called Manager, which will be holding the event records. A record (row) here will comprise the table primary key, timestamp, product name, event text message, reorder level and current product level.

cd c:
"C:Program FilesMySQLMySQL Server 5.1binmysql" -u root -p
Enter password: sql
USE wholesale;

When I say, stop the database, drop the connection, and stop the server, you should execute the following command at the command prompt:

QUIT

- Start the database server and connect to it; choose the wholesale database.

- Type and execute the following compound statement, at the mysql command prompt:

CREATE TABLE Manager
(
    EventID INTEGER NOT NULL AUTO_INCREMENT,
    DateAndTime TIMESTAMP,
    ProductName CHAR(40),
    EventMessage CHAR(60) DEFAULT 'has gone below reorder level',
    ReorderLevel INTEGER,
    CurrentLevel INTEGER,

    PRIMARY KEY (EventID ASC)
);

Note that the default message string is in single quotes.

- Now, read, type and execute the following to create a trigger:

Delimiter //
CREATE TRIGGER InformManager
AFTER UPDATE
ON Products
FOR EACH ROW
BEGIN
    IF NEW.Quantity <= NEW.ReorderLevel THEN
        INSERT INTO Manager (ProductName, ReorderLevel, CurrentLevel) VALUES (NEW.ProductName, NEW.ReorderLevel, NEW.Quantity);
    END IF;
END;
//

It should have been created and saved. Here, in “NEW.ProductName” and “NEW.ReorderLevel”, ProductName and ReorderLevel are of the Products table and not the manager table. There is a line beginning with IF and another line having “END IF” in the trigger code. I will explain these lines in detail later. For now, just know that these lines check if the quantity (number) of the product for the row is less than or equal to the reorderlevel. If that is the case, then a row will be inserted into the Manager’s table, by the Insert statement between the two lines.

The next thing to do is to execute an Update SQL statement to make the number of a product in the products table a value below the reorder level. Let us do this for the product, Bowl.

- Type and execute the following Update statement:

UPDATE Products SET Quantity = 10 WHERE ProductName = 'Bowl';

Now, we have to check if the trigger was fired and the event recorded in the table, Manager. To do this.

- Read and type the following SELECT statement to see if the trigger was fired and the row inserted into the manager’s table:

SELECT * FROM Manager;

You should see the event record.

Well, it is true that the trigger has solved a problem. However, there are related problems that it has not solved. There is a chain of events that happen beginning from Sales. The sales clerk sells a product and input the information into the Sales and SaleDetails tables. That is already an event, which should have its own trigger. It has to be verified, if the quantity the customer is asking is in stock (products table). So the quantity requested has to be subtracted from the quantity in the Products table, without updating the products table, yet. If the difference of that subtraction does not go below zero, then the selling process can go on; otherwise the customer will be asked if he would take a less quantity, before sales can be made. When the Products table is finally updated, that is when the event and trigger above take place. Do not worry; we shall see how to solve all that as we learn stored procedures in the following parts of the series.

- Stop the database, drop the connection and stop the server.

That is it for this part of the series. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

A Trigger Example in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 5

Division 5

Introduction

Related Links

Comments