1

I work with SpatiaLite, managing databases through SQLite. Unfortunately, this system doesn't allow the use of variables, or at least, I haven't discovered how to use them yet.

My objective is to extract the first four words from a string when it contains "var." or "subsp.", otherwise, just the first two words. The first part of my trigger is doing it properly. The result is stored in the field labeled "NOM_SCIENT_VALID."

Subsequently, I utilize the content of "NOM_SCIENT_VALID" to query several databases. A complication arises at this stage because the value of "NOM_SCIENT_VALID" used to search in the databases is still empty (not updated from the previous UPDATE).

Ideally, I would have preferred to employ a variable to store the outcome of the first part of my trigger and then, use it for the database search.

Do you have any suggestions on how I can resolve this issue?

Here is my trigger :

    CREATE TRIGGER update_espece_trigger
    AFTER UPDATE OF COMPART ON ESPECES_ATTRIBUTS
    FOR EACH ROW
    BEGIN
        
-- Extraction from my string (ESPECE) - stored in NOM_SCIENT_VALIDE 

        UPDATE ESPECES_ATTRIBUTS
        SET NOM_SCIENT_VALIDE = (
            WITH RECURSIVE split(value, str) AS (
                SELECT null, NEW.ESPECE || ' ' FROM ESPECES_ATTRIBUTS WHERE ID = NEW.ID
                UNION ALL
                SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1)
                FROM split WHERE str != ''
            )
            SELECT
                CASE
                    WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) ||
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') ||
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), '')
                    ELSE
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                        COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) 
                END
        )
        WHERE ID = NEW.ID;
   
-- Update of NOM_SCIENT_VALIDE using its own value - From 2 different databases
 
        UPDATE ESPECES_ATTRIBUTS
        SET NOM_SCIENT_VALIDE = (
            SELECT
                CASE
                    WHEN EXISTS (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) THEN
                        (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) = 1 THEN
                        (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) = 0 THEN
                        'Absent'
                    ELSE
                        '(Synonymes)'
                END
        )
        WHERE ID = NEW.ID;
    END;
0

1 Answer 1

1

There was an issue with SELECT CASE, on the second update of NOM_SCIENT_VALIDE.

Here is the updated trigger :

    CREATE TRIGGER update_espece_trigger 
AFTER UPDATE OF COMPART ON ESPECES_ATTRIBUTS 
FOR EACH ROW
BEGIN
    -- Mise à jour de NOM_SIMPLE basée sur NEW.ESPECE
    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SCIENT_VALIDE = (
        WITH RECURSIVE split(value, str) AS (
            SELECT null, NEW.ESPECE || ' ' 
            FROM ESPECES_ATTRIBUTS
            WHERE ID = NEW.ID
            UNION ALL
            SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1)
            FROM split
            WHERE str != ''
        )
        SELECT
            CASE
                WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) ||
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') ||
                     TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), ''))
                ELSE
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                     TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' )) 
            END
    )
    WHERE ID = NEW.ID;

    -- Mise à jour de NOM_SCIENT_VALIDE basée sur la recherche dans TAXREF_FLORE
    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SCIENT_VALIDE = (
        SELECT
            CASE
                WHEN EXISTS (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) THEN
                    (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                ELSE
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) = 1 THEN
                            (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE) = 0 THEN
                            'Absent'
                        ELSE
                            '(Synonymes)'
                    END
            END
    )
    WHERE ID = NEW.ID;
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.