Introduction to Programming MySQL Events

Programming in MySQL – Part 1

MySQL Course

Foreword: In this part of the series, I introduce you to Programming MySQL Events.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 1 of my series, Programming in MySQL. This series is a follow-up of my previous series, Implementing Database in MySQL. So, you should have read that series before coming here. At the bottom of this page you have links to the different series in the course. You should read the different series in the order given.

In this part of the series, I introduce you to Programming MySQL Events. We shall continue with the wholesale database we had in the former series. We shall also use other tables that are not in that database.

Event
An event is an action that depends on time. 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 that table, to indicate the product concerned, the reorder level, the current level (number of products) and the timestamp. When the highest manager sees such a new row information in his table, it is up to him to take a decision.

It is possible to code such event handler with SQL. An event handler is a group of SQL statements called a trigger. When the work is too much for a trigger, another type of group of SQL statements called Stored Procedure can be called to assist. Before you can code event handlers, you will have to learn more on SQL.

What is really SQL?
SQL stands for Structured Query Language. This is a computer language that a database programmer has to learn. The language consists of statements called SQL statements and each of these statements ends with a semicolon. The SQL statements you have learned so far in the previous series are:

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE USER
DROP USER
GRANT
INSERT
UPDATE
DELETE
SELECT
ALTER USER

There are many more SQL statements. You will learn some more in this series in order to write code for event handlers.

That is it, for introduction on coding event handlers with SQL. Let us stop here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Introduction to Programming MySQL Events

Programming in MySQL – Part 1

MySQL Course

Introduction

Related Links

Comments