An Event Sequence Project in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 15
Forward: In this part of the series, we carry out a project on a sequence of events in Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 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.
Savepoint and Rollback
A SQL program is made up of SQL statements typed one after the other. In a connection, after executing a number of statements, you may realize that something has gone wrong and you would want to undo a number of statements (reverse the changes) going backwards.
Within the program, there are regions (groups) of the statements where you suspect, something can go wrong. At the beginning of that region you have to establish a Savepoint. Where you know the risk is finished you type a conditional Rollback statement, which will rollback (undo) the changes backward up to the Savepoint. The conditional rollback statement will check if an error occurred. If an error occurred it will rollback. If no error occurred, it will not rollback and the changes will stay. After that you need a statement to release the Savepoint. The SQL statements below the release of Savepoint will continue executing normally. If you do not release the Savepoint just after the rollback, then some rollback statement down in the code will rollback up to this Savepoint. Rollback here means Rollback to savepoint. So you have something like this for your SQL statements:
-- SQL statements
SavePoint
-- SQK statements
IF something goes wrong THEN
Rollback
END IF
Release Savepoint.
The SQL statement to establish a savepoint is:
SAVEPOINT [ savepoint-name ];
savepoint-name is a name of your choice. The syntax to rollback to the established savepoint is:
ROLLBACK TO SAVEPOINT [ savepoint-name ];
savepoint-name here is the name you give for the SAVEPOINT statement above. The syntax to release a savepoint is:
RELEASE SAVEPOINT [ savepoint-name ]
savepoint-name is the same name for the two SQL statements above. It is optional. If you do not give a name, rollback will go to the first savepoint it meets as it goes back.
We are still dealing the wholesale database. The tables involve in this project are the Sales table, the SaleDetails and the Products table. For simplicity, let us assume that when a customer requests a number of products, if for each product, all the quantity for that product is not available, then the customer will not buy anything. That is, if this problem occurs, the customer will not buy any of the products.
For any sale there is at least one product requested by the customer. The SaleDetails table has a trigger that response to the Insert event. When a row is inserted the trigger will call a procedure that will check if the quantity of the product demanded is available in the products table. The procedure will send a feedback to the trigger. If the quantity is available, then the trigger will update the Products table otherwise the trigger will rollback all the changes that have taken place in the sales, issuing an error message to the user. This rollback will be done only after all the SaleDetail rows have been taken into consideration. This rollback is actually ROLLBACK TO SAVEPOINT. When it takes place, any change that took place in the Sales, SaleDetails and Products table will be undone.
Before sales starts, a Savepoint is established. After sales (and sale details) the Savepoint is released.
For simplicity, our user is using the Interactive SQL window. To send the user a feedback, we shall use the SELECT statement (DIY - in the trigger code).
Remember, the Product table already has a trigger we defined in one of the previous parts of the series, which would send a record (row) to the manager’s table informing him of any product whose quantity has gone down below its reorder level.
With all the technical description given above, you can now write the code, but we shall do it together.
In this project we assume that all product names are unique.
The code has a section that will hold the variables for the Sale and SaleDetails data. In practice these data will be entered in a Form at the user’s computer. The code also has a long section for inserting one row in the Sales table and several rows in the SaleDetails table, assuming that a customer would buy several products. This section is begun by the establishment of a Savepoint and ended by the release of the Savepoint. The Rollback is issued at the end of the SaleDetails trigger.
There is a code section for the SaleDetails trigger. This trigger will respond to every row that is inserted into the SaleDetails table. When a row is inserted it would call the procedure, which would check if the quantity of the product demanded is available. The feedback it sends to the trigger is the number of products that would be left if the product were sold. This trigger will allow all the rows of the SaleDetails to be inserted, then if a quantity were short, it would rollback to Savepoint, otherwise there is no rollback and the inserted tables are allowed. If it rolls back, it informs the user (DIY).
There is a section, which is the procedure that will determine if the quantity of the product demanded is available in the products table. This procedure will send a feedback to the SaleDetails trigger indicating the number that would be left if the particular product were sold.
There is the section for the trigger of the Products table. This one has already been written. It does not affect any of the above sections.
Table Notations
These are the notations of the tables involved:
Sales(SaleID, DateAndTime, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, Qty, SoldPrice)
Products(ProductID, ProductName, Category, Number, ReorderLevel, CostPrice, SellingPrice)
There are a good number of variables for the program. Some are for the input data and others are just to enable the program function. I will not list all the variables in this section. This is the code section for the main variables:
-- The Sale data
CREATE VARIABLE EmployeeIDVar SMALLINT = 3;
CREATE VARIABLE CustomerIDVar SMALLINT= 2;
-- SaleDetails data
CREATE VARIABLE productNameVar1 CHAR(40) = 'Bowl';
CREATE VARIABLE QtyVar1 INTEGER = 5;
CREATE VARIABLE productNameVar2 CHAR(40) = 'Pen';
CREATE VARIABLE QtyVar2 INTEGER = 12;
CREATE VARIABLE productNameVar3 CHAR(40) = 'Plate';
CREATE VARIABLE QtyVar3 INTEGER = 7;
CREATE VARIABLE numberOfRows SMALLINT = 3;
For simplicity let the SoldPrice be the SellingPrice got from the Products table. So the customer has ordered three products (number of rows).
The following sub section creates a temporary table and inserts the variable data of the SaleDetails table there. The temporary table is not necessary here for an only SQL program, but you will likely need it when the user is inputting information in the windows Form. When the user puts data in the windows Form, the data will come and be in the temporary table first, before being read and inserted into the actual SaleDetails table. Read through the above code segment if you have not already done so.
-- SaleDetails temporary table
CREATE TABLE #SaleDetailsTemp
(
ID SMALLINT DEFAULT AUTOINCREMENT,
ProductName CHAR(40),
Qty INTEGER
)
INSERT INTO #SaleDetailsTemp (ProductName, Qty) VALUES (productNameVar1, QtyVar1);
INSERT INTO #SaleDetailsTemp (ProductName, Qty) VALUES (productNameVar2, QtyVar2);
INSERT INTO #SaleDetailsTemp (ProductName, Qty) VALUES (productNameVar3, QtyVar3);
Read the above code if you have not already done so.
This is the code section for inserting the rows into the Sales and SaleDetails tables. Variables that are useful in this section are created here.
-- Section for Inserting the Rows
SAVEPOINT riskStart;
CREATE VARIABLE i INTEGER = 1; -- loop iteration (repetition) variable
CREATE VARIABLE ProductIDVar SMALLINT;
-- Create a variable to hold any product name of the temporary table
CREATE VARIABLE PNV CHAR(40);
-- Create a variable to hold any quantity of the temporary table
CREATE VARIABLE QV INTEGER;
-- Create a variable to hold any ProductID of the Products table
CREATE VARIABLE PIDV SMALLINT;
-- Create a variable to hold any SellingPrice of the Products table
CREATE VARIABLE SoldPriceVar NUMERIC(19,2);
-- Create a variable to hold return value from the procedure
CREATE VARIABLE returnVar INTEGER;
-- Create a variable to hold the number of times the trigger operates on this insert
CREATE VARIABLE n INTEGER = 1;
-- Create a variable to indicate shortage of product
CREATE VARIABLE remainingVar CHAR(10);
INSERT INTO Sales (EmployeeID, CustomerID) VALUES (EmployeeIDVar, CustomerIDVar);
CREATE VARIABLE SaleIDVar SMALLINT; -- Determine by Sales table
SELECT MAX(SaleID) AS SaleIDVar
INTO SaleIDVar
FROM Sales;
WHILE i <= numberOfRows LOOP
SELECT ProductName AS PNV, Qty AS QV
INTO PNV, QV
FROM #SaleDetailsTemp
WHERE ID = i;
SELECT ProductID AS PIDV, SellingPrice AS SoldPriceVar
INTO PIDV, SoldPriceVar
FROM Products
WHERE ProductName = PNV;
INSERT INTO SaleDetails (SaleID, ProductID, Qty, SoldPrice) VALUES (SaleIDVar, PIDV, QV, SoldPriceVar);
SET i = i + 1;
END LOOP;
RELEASE SAVEPOINT riskStart;
The LOOP reads data from the temporary table and the Products table and inserts into the SaleDetails table appropriately. Read through the above code segment if you have not done so.
Here is the trigger code for the SaleDetails.
CREATE TRIGGER SaleDetailsTrig
AFTER INSERT
ORDER 2 ON SaleDetails
REFERENCING NEW AS newRow
FOR EACH ROW
BEGIN
Call availableQuantity (newRow.Qty, newRow.ProductID, returnVar);
SET n = n+1;
IF returnVar < 0 THEN
SET remainingVar = 'short';
ELSE
UPDATE Products SET NUMBER = returnVar WHERE ProductID = newRow.ProductID;
END IF;
IF n = (numberOfRows+1) THEN
SET n = 1;
END IF;
IF (n = numberOfRows+1) AND (remainingVar = 'short') THEN
ROLLBACK TO SAVEPOINT riskStart;
END IF;
END;
The first statement in the compound statement calls the procedure. The second statement is used to count the number of times the trigger is executed for the SaleDetails rows inserted for the particular customer. The first IF statement checks if the subtraction that the procedure does between the quantity requested and the quantity available is less than zero. If it is, it assigns the string, ‘short’ to the variable, remainingVar. Else it updates the Products table with the new quantity value.
The next IF statement is used to reset the variable that counts the number of times the trigger is executed. This reset is done after all the number of rows for products demanded have been inserted. The last IF statement checks if all the number of rows for products demanded have been inserted and if any of the quantities demanded was short. If both conditions are true, then the statements are rolled back to undo the changes. It is here that you should place a statement to inform the user of the shortage. The changes are undone for all the tables that were affected (SaleDetails, Sales and Products) after the Savepoint was established.
This saleDetails trigger code section should be typed before the section for inserting the rows.
The Stored Procedure
This is the code for the stored procedure.
CREATE PROCEDURE availableQuantity
(
IN qtyDemand INTEGER,
IN ProdID SMALLINT,
OUT answer INTEGER
)
BEGIN
-- Declare a variable to indicate product quantity available in Products table
DECLARE availableQtyVar INTEGER;
SELECT NUMBER AS availableQtyVar
INTO availableQtyVar
FROM Products
WHERE ProductID = ProdID;
-- Subtract quantity demanded from quantity available
SET answer = availableQtyVar - qtyDemand;
END;
The first statement in the compound statement declares a variable to be used inside the compound statement. This variable ceases to exist as the compound statement ends its operation. Next, the quantity available is selected. Next, the quantity demanded is subtracted from the quantity available. The result, answer, would be read out. If this answer is negative, it means that there is not enough quantity available, and the trigger should finally rollback. The procedure should be typed before the trigger.
The complete code can be seen at the link below.
Well, this has been a long ride. Let us end here and continue in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem