Introduction to MySQL Programming for Events
Handling MySQL Events with Triggers and Procedures Using SQL – Part 1
Division 5
Forward: In this part of the series, I introduce you to MySQL SQL Programming for Events. We are dealing with MySQL 5.1.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
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.
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 this 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 triggers. 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 we can code event handlers we shall 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 statement ends with a semicolon. The SQL statements we 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. We shall learn some of them 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
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT