Home › Tutorials › An Event Sequence Project in Sybase Sql Anywhere

An Event Sequence Project in Sybase Sql Anywhere

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.

Section for Inserting the Rows
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.

The SaleDetails Trigger
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 downloaded form,

http://www.cool-mathematics.biz/downloads/An-Event-Sequence-Project-in-Sybase-SQL-Anywhere.zip

Well, this has been a long ride. Let us end here and continue in the next part of the series.

Chrys

To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).

Handling Sybase Events with Triggers and Procedures Using SQL – Part 1
Handling Sybase Events with Triggers and Procedures Using SQL – Part 2
Handling Sybase Events with Triggers and Procedures Using SQL – Part 3
Handling Sybase Events with Triggers and Procedures Using SQL – Part 4
Handling Sybase Events with Triggers and Procedures Using SQL – Part 5
Handling Sybase Events with Triggers and Procedures Using SQL – Part 6
Handling Sybase Events with Triggers and Procedures Using SQL – Part 7
Handling Sybase Events with Triggers and Procedures Using SQL – Part 8
Handling Sybase Events with Triggers and Procedures Using SQL – Part 9
Handling Sybase Events with Triggers and Procedures Using SQL – Part 10
Handling Sybase Events with Triggers and Procedures Using SQL – Part 11
Handling Sybase Events with Triggers and Procedures Using SQL – Part 12
Handling Sybase Events with Triggers and Procedures Using SQL – Part 13
Handling Sybase Events with Triggers and Procedures Using SQL – Part 14
Handling Sybase Events with Triggers and Procedures Using SQL – Part 15
Handling Sybase Events with Triggers and Procedures Using SQL – Part 16