A Trigger Example in Sybase SQL Anywhere

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

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

By: Chrysanthus Date Published: 27 Aug 2012

Introduction

This is part 5 of my series, Handling Sybase Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big 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 Sybase SQL Anywhere.

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, Number, CostPrice, SellingPrice)

Let us add another column to it called, ReorderLevel; to have,

Products(ProductID, ProductName, Category, Number, 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 number (quantity) column for each row is reducing. As soon as the value in the number 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).

In order to write the code for this example, we shall have to drop the Products table and create a new one with the ReorderLevel column, and then feed it with data. 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 and connect to the database.
- Type and execute,

DROP TABLE Products;

- Create the new table by typing and executing the following (erase whatever was in the SQL Statements pane first):

CREATE TABLE Products
(
    ProductID SMALLINT DEFAULT AUTOINCREMENT,
    ProductName CHAR(40) INDEX,
    Category CHAR(30) INDEX,
    NUMBER INTEGER,
    ReorderLevel INTEGER,
    CostPrice NUMERIC(19,2),
    SellingPrice NUMERIC(19,2),

    PRIMARY KEY (ProductID ASC)
);

Insert data into the Products table with the following statements:

INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pen', 'Office', '150', '20', '0.5', '0.75');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Books', 'Office', '500', '25','3', '4');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Bowl', 'Household', '175', '30','2', '3');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Spoon', 'Household', '300', '35','.1', '.2');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Plate', 'Household', '450', '40','1', '1.25');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('TV Set', 'Entertainment', '160', '45', '25', '30');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('VCD', 'Entertainment', '200', '50', '20', '25');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Clothe Box', 'Household', '250', '55','16', '21');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Perfume', 'Beauty', '258', '60', '2', '3');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Banana', 'Fruit', '205', '65', '5', '7');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pear', 'Fruit', '150', '70','3', '4');

Now, type the following to create the table Manager table (erase any previously typed statement):

CREATE TABLE Manager
(
    EventID INTEGER DEFAULT AUTOINCREMENT,
    DateAndTime TIMESTAMP DEFAULT 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 and type the following to create a trigger (erase any previously typed statement):

CREATE TRIGGER InformManager
AFTER UPDATE
ORDER 2 ON Products
REFERENCING NEW AS newRow
FOR EACH ROW
BEGIN
    IF newRow.Number <= newRow.ReorderLevel THEN
        INSERT INTO Manager (ProductName, ReorderLevel, CurrentLevel) VALUES (newRow.ProductName, newRow.ReorderLevel, newRow.Number);
    END IF;
END;

- Create the new table by typing and executing the following (erase whatever was in the SQL Statements pane first):

CREATE TABLE Products
(
    ProductID SMALLINT DEFAULT AUTOINCREMENT,
    ProductName CHAR(40) INDEX,
    Category CHAR(30) INDEX,
    NUMBER INTEGER,
    ReorderLevel INTEGER,
    CostPrice NUMERIC(19,2),
    SellingPrice NUMERIC(19,2),

    PRIMARY KEY (ProductID ASC)
);

- Insert data into the Products table with the following statements:

INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pen', 'Office', '150', '20', '0.5', '0.75');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Books', 'Office', '500', '25','3', '4');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Bowl', 'Household', '175', '30','2', '3');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Spoon', 'Household', '300', '35','.1', '.2');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Plate', 'Household', '450', '40','1', '1.25');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('TV Set', 'Entertainment', '160', '45', '25', '30');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('VCD', 'Entertainment', '200', '50', '20', '25');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Clothe Box', 'Household', '250', '55','16', '21');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Perfume', 'Beauty', '258', '60', '2', '3');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Banana', 'Fruit', '205', '65', '5', '7');
INSERT INTO Products (ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice) VALUES ('Pear', 'Fruit', '150', '70','3', '4');

- Now, type the following to create the table Manager table (erase any previously typed statement):

CREATE TABLE Manager
(
    EventID INTEGER DEFAULT AUTOINCREMENT,
    DateAndTime TIMESTAMP DEFAULT 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 and type the following to create a trigger (erase any previously typed statement):

CREATE TRIGGER InformManager
AFTER UPDATE
ORDER 2 ON Products
REFERENCING NEW AS newRow
FOR EACH ROW
BEGIN
    IF newRow.Number <= newRow.ReorderLevel THEN
        INSERT INTO Manager (ProductName, ReorderLevel, CurrentLevel) VALUES (newRow.ProductName, newRow.ReorderLevel, newRow.Number);
    END IF;
END;

It should have been created and saved. Here, in “newRow.ProductName” and “newRow.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, Spoon.

- Read and type the following Update statement (erase first):

UPDATE Products SET Number = 10 WHERE ProductName = 'Spoon';

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 (erase first):

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.

- Drop the connection, stop the database, 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 Sybase SQL Anywhere

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

Introduction

Related Courses

Comments