Introduction to Programming MySQL Events
Programming in MySQL – Part 1
Foreword: In this part of the series, I introduce you to Programming MySQL Events.
By: Chrysanthus Date Published: 27 May 2015
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.
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.
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:
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.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course