How to add an item in a "transaction" table and modifying a value in another table by calculation

English
, ,
#1

Hi everybody,

(LibreOffice 6.2, Integrated Firebird )

I have a table of objects with different informations but especially the current stock of these objects.
I have another “Transaction” table in which I encode the references of the sales/productions transaction of these objects (date, customer, financial transation reference, number of objects sold or produced, …)
When adding these transactions, I would like to modify the stock value of the object in the “Object” table :
Production = Add the number to the current stock,
Sale = Removing the number

Is there a simple way in the forms ?
Do I have to use a macro ?
Can you direct me to the right way ?

Thank you in advance!

#4

Thanks to Ratslinger, I was able to work in the right direction and it seems that the following code does what I expect it to do. Thank you very much !
It is certainly not perfect, the logic of the algorithm could have been better and for example, I realize that the Updates of the main IF THEN ELSE, could only exist in one copy … but I don’t have the energy to check anymore, I now have to fill my database :-p
So, I post the code here (without DB structure) because I think it would have been useful about the syntax in particular, so some may be inspired by it.

Trigger for INSERT and UPDATE (AFTER)

CREATE TRIGGER ProduitsEtAppretsStock FOR "Transactions"
ACTIVE AFTER INSERT OR UPDATE POSITION 1
AS
DECLARE aprid INT ;
DECLARE aprst INT ;
DECLARE aprstnew INT ;
DECLARE aprnbr SMALLINT ;
DECLARE prdid INT ;
DECLARE prdst INT ;
DECLARE prdstnew INT ;
DECLARE trsnb SMALLINT ;
DECLARE trstp SMALLINT = 1 ;
DECLARE trsprevnb SMALLINT ;
BEGIN
trsnb = NEW."TransactionNbrUnite" ;
IF (NEW."TransactionTypeID" > 2) THEN trstp = -1 ;
IF (NEW."AppretID" IS NOT NULL) THEN
    /* Transaction sur un Apprêt */
    BEGIN
        aprid = NEW."AppretID" ;
        SELECT "AppretStock" FROM "Apprets" WHERE "AppretID" = :aprid INTO :aprst ;
        IF (inserting) THEN
            BEGIN
            /* Dans le cas d'une Insertion pas d'ancienne valeur du champs */
            aprstnew = (aprst + ( trsnb * trstp )) ;
            END
        ELSE
            BEGIN
            /* Dans le cas d'un Update il faut l'ancienne valeur pour corriger le stock */
            trsprevnb = old."TransactionNbrUnite" ;
            aprstnew = (aprst + (( trsnb - trsprevnb ) * trstp )) ;
            END
        UPDATE "Apprets" SET "AppretStock" = :aprstnew WHERE "AppretID" = :aprid ;
    END
ELSE
    /* Transaction sur un Produit */
    BEGIN
        prdid = NEW."ProduitID" ;
        SELECT "ProduitStock" FROM "Produits" WHERE "ProduitID" = :prdid INTO :prdst ;
        IF (inserting) THEN
            BEGIN
            /* Dans le cas d'une Insertion pas besoin de reprendre l'ancienne valeur */
            prdstnew = (prdst + ( trsnb * trstp )) ;
            IF (NEW."TransactionTypeID" = 2) THEN
                BEGIN
                    FOR SELECT DISTINCT "JonctionNbreAppret"."AppretID", "JonctionNbreAppret"."AppretNbre"
                            FROM    "JonctionNbreAppret", "Apprets", "Produits"
                            WHERE   "JonctionNbreAppret"."AppretID" = "Apprets"."AppretID" AND "JonctionNbreAppret"."ProduitID" = "Produits"."ProduitID" AND "Produits"."ProduitID" = :prdid
                        INTO :aprid, :aprnbr
                    DO
                        BEGIN
                            SELECT "AppretStock" FROM "Apprets" WHERE "AppretID" = :aprid INTO :aprst ;
                            aprstnew = (aprst - (trsnb * aprnbr)) ;
                            UPDATE "Apprets" SET "AppretStock" = :aprstnew WHERE "AppretID" = :aprid ;
                        END
                END
            END
        ELSE
            BEGIN
            /* Dans le cas d'un Update il faut l'ancienne valeur pour corriger le stock */
            trsprevnb = old."TransactionNbrUnite" ;
            prdstnew = (prdst + (( trsnb - trsprevnb ) * trstp )) ;
            IF (NEW."TransactionTypeID" = 2) THEN
                BEGIN
                    FOR SELECT DISTINCT "JonctionNbreAppret"."AppretID", "JonctionNbreAppret"."AppretNbre"
                            FROM    "JonctionNbreAppret", "Apprets", "Produits"
                            WHERE   "JonctionNbreAppret"."AppretID" = "Apprets"."AppretID" AND "JonctionNbreAppret"."ProduitID" = "Produits"."ProduitID" AND "Produits"."ProduitID" = :prdid
                        INTO :aprid, :aprnbr
                    DO
                        BEGIN
                            SELECT "AppretStock" FROM "Apprets" WHERE "AppretID" = :aprid INTO :aprst ;
                            aprstnew = (aprst - ((trsnb - trsprevnb) * aprnbr)) ;
                            UPDATE "Apprets" SET "AppretStock" = :aprstnew WHERE "AppretID" = :aprid ;
                        END
                END
            END
        UPDATE "Produits" SET "ProduitStock" = :prdstnew WHERE "ProduitID" = :prdid ;
    END
END

Trigger for DELETE (BEFORE)

CREATE TRIGGER SuppressionTransactionStocks FOR "Transactions"
ACTIVE BEFORE DELETE POSITION 0
AS
DECLARE aprid INT ;
DECLARE aprst INT ;
DECLARE aprstnew INT ;
DECLARE aprnbr SMALLINT ;
DECLARE prdid INT ;
DECLARE prdst INT ;
DECLARE prdstnew INT ;
DECLARE trstp SMALLINT = 1 ;
DECLARE trsprevnb SMALLINT ;
BEGIN
trsprevnb = OLD."TransactionNbrUnite" ;
IF (OLD."TransactionTypeID" > 2) THEN trstp = -1 ;
IF (OLD."AppretID" IS NOT NULL) THEN
    /* Transaction sur un Apprêt */
    BEGIN
        aprid = OLD."AppretID" ;
        SELECT "AppretStock" FROM "Apprets" WHERE "AppretID" = :aprid INTO :aprst ;
        aprstnew = (aprst - (trsprevnb * trstp )) ;
        UPDATE "Apprets" SET "AppretStock" = :aprstnew WHERE "AppretID" = :aprid ;
    END
ELSE
    /* Transaction sur un Produit */
    BEGIN
        prdid = OLD."ProduitID" ;
        SELECT "ProduitStock" FROM "Produits" WHERE "ProduitID" = :prdid INTO :prdst ;
        prdstnew = (prdst - (trsprevnb * trstp )) ;
        UPDATE "Produits" SET "ProduitStock" = :prdstnew WHERE "ProduitID" = :prdid ;
        IF (OLD."TransactionTypeID" = 2) THEN
            BEGIN
                FOR SELECT DISTINCT "JonctionNbreAppret"."AppretID", "JonctionNbreAppret"."AppretNbre"
                        FROM    "JonctionNbreAppret", "Apprets", "Produits"
                        WHERE   "JonctionNbreAppret"."AppretID" = "Apprets"."AppretID" AND "JonctionNbreAppret"."ProduitID" = "Produits"."ProduitID" AND "Produits"."ProduitID" = :prdid
                    INTO :aprid, :aprnbr
                DO
                    BEGIN
                        SELECT "AppretStock" FROM "Apprets" WHERE "AppretID" = :aprid INTO :aprst ;
                        aprstnew = (aprst + (trsprevnb * aprnbr)) ;
                        UPDATE "Apprets" SET "AppretStock" = :aprstnew WHERE "AppretID" = :aprid ;
                    END
            END
    END
END
#5

@Auroch,

Thanks for the post. +1. From past experience know this is mentally tiring.

Just an observance, you may want to consider adding DELETE to the possible actions.

#6

@Ratslinger Indeed, I don’t use delete in the form managing this table but it will be usefull in case of error. I’ll correct this soon.

#7

Indeed, I don’t use delete in the form managing this table but it will be usefull in case of error. I’ll correct this soon.

#8

Updated and Added DELETE Trigger

#2

Hello,

This is more of a database function than it is a Base action. You cannot do this in a simple manner. You can use macros but it will require much planning to cover all aspects.

Since this is an occurrence of a ‘transaction’ record it may be best to do this using a “Trigger” in the database. You will need to review the documentation (see → Firebird triggers ) on the construction. It will require time on your part and testing.

A very simple trigger example can be found here → Base 6.2.8.2 with embedded Firebird - Triggers?

#3

Great, that does indeed sound like the right way to go.
Thank you Ratslinger!
I’ll run some tests with great care and backups ^^.
As soon as it works, I’ll come and post the example of what I do, it will probably be useful to others.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.