1

I currently have two SpatiaLite layers:

HABITATS_POLY: Key fields include EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4, EUNIS22_1, EUNIS22_2, EUNIS22_3, EUNIS22_4.

GESTION_CODES: Key fields include EUNIS and EUNIS22.

I am attempting to create a trigger that will automatically update the fields EUNIS22_1, EUNIS22_2, EUNIS22_3, and EUNIS22_4 in the HABITATS_POLY table based on the values in the EUNIS22 field from the GESTION_CODES table.

The trigger conditions are as follows: - WHEN EUNIS equals EUNIS_1, set EUNIS22_1 to the value of EUNIS22. - WHEN EUNIS equals EUNIS_2, set EUNIS22_2 to the value of EUNIS22. - WHEN EUNIS equals EUNIS_3, set EUNIS22_3 to the value of EUNIS22. - WHEN EUNIS equals EUNIS_4, set EUNIS22_4 to the value of EUNIS22.

I can make it work when I update a field in HABITATS_POLY, by using this trigger:

CREATE TRIGGER update_eunis22_codes 
AFTER UPDATE OF EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4 ON HABITATS_POLY 
BEGIN 
    UPDATE HABITATS_POLY 
        SET EUNIS22_1 = (SELECT "EUNIS22" FROM GESTION_CODES 
            WHERE "EUNIS" = NEW.EUNIS_1), 
        EUNIS22_2 = (SELECT "EUNIS22" FROM GESTION_CODES 
            WHERE "EUNIS" = NEW.EUNIS_2), 
        EUNIS22_3 = (SELECT "EUNIS22" FROM GESTION_CODES 
            WHERE "EUNIS" = NEW.EUNIS_3), 
        EUNIS22_4 = (SELECT "EUNIS22" FROM GESTION_CODES 
            WHERE "EUNIS" = NEW.EUNIS_4) 
    
    WHERE EUNIS_1 = NEW.EUNIS_1 OR 
    EUNIS_2 = NEW.EUNIS_2 OR 
    EUNIS_3 = NEW.EUNIS_3 OR 
    EUNIS_4 = NEW.EUNIS_4; 
END;

When I insert one feature at a time, I can also have the results I want.

However, when I attempt to insert multiple features simultaneously, only one occurrence of each code is being updated. For instance, if I add 10 features with the EUNIS code 'A1.1' (corresponding to EUNIS22 code 'MA1-46'), only one row gets updated with the EUNIS22 code, while the others remain empty.

I've experimented with various approaches, but so far, I haven't found a solution to this issue.

Here is an example of the result for 7 features with 2 different EUNIS codes. You can see, only 2 features have been updated. Example

Here is the last trigger I tried:

CREATE TRIGGER update_eunis22_gest
AFTER UPDATE OF EUNIS22 ON GESTION_CODES
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET
        EUNIS22_1 = NEW.EUNIS22
    WHERE
        EUNIS_1 IN (SELECT EUNIS FROM GESTION_CODES WHERE NEW.EUNIS = EUNIS);

    UPDATE HABITATS_POLY
    SET
        EUNIS22_2 = NEW.EUNIS22
    WHERE
        EUNIS_2 IN (SELECT EUNIS FROM GESTION_CODES WHERE NEW.EUNIS = EUNIS);

    UPDATE HABITATS_POLY
    SET
        EUNIS22_3 = NEW.EUNIS22
    WHERE
        EUNIS_3 IN (SELECT EUNIS FROM GESTION_CODES WHERE NEW.EUNIS = EUNIS);

    UPDATE HABITATS_POLY
    SET
        EUNIS22_4 = NEW.EUNIS22
    WHERE
        EUNIS_4 IN (SELECT EUNIS FROM GESTION_CODES WHERE NEW.EUNIS = EUNIS);
END;

1 Answer 1

1

The solution I found is to add another trigger when I insert data in the layer HABITATS_POLY.

Trigger:

CREATE TRIGGER insert_eunis22_codes 
AFTER INSERT ON HABITATS_POLY 
BEGIN 
UPDATE HABITATS_POLY 
SET EUNIS22_1 = (SELECT "EUNIS22" FROM GESTION_CODES 
WHERE "EUNIS" = NEW.EUNIS_1), 
EUNIS22_2 = (SELECT "EUNIS22" 
FROM GESTION_CODES 
WHERE "EUNIS" = NEW.EUNIS_2), 
EUNIS22_3 = (SELECT "EUNIS22" 
FROM GESTION_CODES WHERE "EUNIS" = NEW.EUNIS_3), 
EUNIS22_4 = (SELECT "EUNIS22" 
FROM GESTION_CODES WHERE "EUNIS" = NEW.EUNIS_4) 

WHERE EUNIS_1 = NEW.EUNIS_1 OR
 EUNIS_2 = NEW.EUNIS_2 OR
 EUNIS_3 = NEW.EUNIS_3 OR
 EUNIS_4 = NEW.EUNIS_4; 
END;

That's a lot of triggers.. Especially if I need to work with several fields.

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.