MySQL Triger

Programming in MySQL – Part 3

MySQL Course

Foreword: In this part of the series, we look at the nature of triggers in MySQL and we look at a trigger example.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 3 of my series, Programming in MySQL. In this part of the series, we look at the nature of triggers in MySQL and we look at a trigger example. You should have read the previous parts of the series before reaching here, as this is a continuation.

You will use the PurePerl MySQL Command Line Tool. The link to its free download is given below, with the title, PurePerl MySQL API .

A trigger
Consider a trigger as an event handler. An event occurs, code is executed; that code is the event handler. When a trigger is executed we say the trigger is fired.

Trigger Execution Privileges
A trigger is associated with a table. To make things simple, let the Database Administrator (DBA) code the trigger in this series. It is possible that a trigger may access some other table. Let the user of the trigger (and of course its table), have the privilege to access the other table to be accessed by the trigger. We shall discuss security issues in an entirely different series.

Trigger Events
A trigger is fired immediately an event occurs. A trigger is defined for one of the following events:

INSERT: Invokes the trigger whenever a new row is inserted into the table associated with the trigger.
DELETE: Invokes the trigger whenever a row associated with the table is deleted.
UPDATE: Invokes the trigger whenever a row associated with the table is updated.

Trigger Times
BEFORE trigger: A BEFORE trigger fires before a triggering action is performed; that is before the event of INSERT, DELETE or UPDATE is performed.

AFTER trigger: An AFTER trigger fires after the triggering action is complete; that is after the event of INSERT, DELETE or UPDATE has been performed.

Trigger and Compound Statement
The core code of a trigger is a compound statement.

When does a Trigger Fire?
A trigger is associated with a table, and it fires when an update, insert or delete event occurs. So a trigger is fired automatically as soon as any of these events occur. Remember that a trigger has to be written first by the programmer. If no trigger is written, nothing is fired.

The CREATE TRIGGER Syntax
The CREATE TRIGGER Statement is used to create a trigger. Once you use this statement to create a trigger, the trigger is saved. You do not need any special command to save the trigger. The table name typed in the create trigger statement relates the trigger to the table. A simplified CREATE TRIGGER syntax is,

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body

Remember, for SQL syntaxes, [] means optional and | means and/or.

trigger_name
trigger-name is the name of your choice you give to the trigger.

trigger_time
trigger-time is the keyword, BEFORE or AFTER and is to do with the timing.

trigger_event
This is the Delete, or Update or Insert event.

Note: There cannot be two triggers for a given table that have the same trigger time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

tbl_name
This is the name of the table associated with the trigger.

FOR EACH ROW
A MySQL trigger acts on a row. So this phrase has to be there.

trigger_body
This is the compound statement.

Referencing Old or New Row
You have the Insert row event and the delete row event. The Update process is equivalent to deleting a row and then inserting a new one. In the update process, you refer to the deleted row as OLD in the trigger_body; you refer to the inserted row as NEW. If you want to access any cell in the OLD row, the syntax is:

    OLD.col-name

If you want to access any cell in the NEW row, the syntax is:

    NEW.col-name

Note the use of the dot.

So far as the CREATE TRIGGER syntax is concerned, the INSERT event can only have a NEW row, since during Insert, a new row is inserted with no old row deleted. Also the Delete event can only have an OLD row, since during Delete, the old row is deleted and no new row is added.

Note: There is no semicolon after the instructions in the CREATE TRIGGER statement before the BEGIN statement.

Note: With triggers, after the Insert or Update or Delete has taken place, you can reverse the event. I will address that later.

Dropping a Trigger
You can drop (erase) a trigger. You use the DROP TRIGGER statement for this. In simple terms, the syntax is:

    DROP TRIGGER trigger_name;

A Trigger Example

For the rest of this tutorial, we look at an example of a trigger in MySQL.

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 cell reaches or goes below the value in the ReorderLevel 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 a new row 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.

Start the command line tool and connect to the wholesale database with the following guidelines (the MySQL server should already be on):

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

- Type and execute the following statement, at the Mysql command prompt (this creates the manager’s record table):

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

Note that the default message string is in single quotes.

- Now, read, type and execute the following to create a trigger (for the Products table):

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

It should have been created and saved. Here, “NEW.ProductName” and “NEW.ReorderLevel” are of the Products table while ProductName and ReorderLevel are of the Manager’s table. There is a line beginning with IF and another line having “END IF” in the trigger code. Note the use of SET in the INSERT statement. 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 is inserted into the Manager’s table, by the Insert statement between the two lines.

To try the trigger code, we have 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,

- Type the following SELECT statement to see if the row was 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 the trigger cannot solve. 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 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; you will see how to solve all that as we discuss stored procedures in the following parts of the series.

Type the following commands to close the connection and quit the command line tool.

    close
    quit

The server is still on though.

The full syntax to create a trigger is as follows. Use it when you want to include the user of the table, who is not the root.

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

where

trigger_time is

    { BEFORE | AFTER }

and

trigger_event is

    { INSERT | UPDATE | DELETE }

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

Chrys

Broad Network

Related Articles

MySQL Triger

Programming in MySQL – Part 3

MySQL Course

Introduction

A Trigger Example

Related Links

Comments