0

See edit at the end of the post

I am working in two layers:

HABITATS_POLY (important fields: "EUNIS_1" , "EUNIS_2" , "EUNIS_3" , "EUNIS_4" ; "EUNIS22_1" , "EUNIS22_2" , "EUNIS22_3" , "EUNIS22_4")

GESTION_CODES (important fields: "EUNIS", "EUNIS22"

And one database:

BD_EUNIS22

First I update HABITATS_POLY by inserting or updating rows:

On [HABITATS_POLY]

INSERTION:

[trigger's name: insert_eunis_gest]

Insertion of new rows in HABITATS_POLY when ("EUNIS_1" , "EUNIS_2" , "EUNIS_3" , "EUNIS_4") does not exists in GESTION_CODES ("EUNIS")

[trigger's name: insert_eunis22_codes]

After insertion on HABITATS_POLY then UPDATE EUNIS22_1, EUNIS22_2, EUNIS22_3, EUNIS22_4 from EUNIS22 (GESTION_CODES) where EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4 (HABITATS_POLY) = EUNIS (GESTION_CODES)

Which triggers insert/updates on GESTION_CODES

[GESTION_CODES]

INSERTION:

[trigger's name: update_eunis22]

WHEN new EUNIS code is inserted in GESTION_CODES then update EUNIS22 = Habitat EUNIS22 [On BD_EUNIS22 : Relation between Habitat EUNIS12 and EUNIS to get Habitat EUNIS22]

UPDATE:

[trigger's name: update_eunis22_gest]

When EUNIS22 is updated then update fields "EUNIS22_1" , "EUNIS22_2" , "EUNIS22_3" , "EUNIS22_4" where EUNIS (GESTION_CODES) = EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4 (HABITATS_POLY)

Which Reset "update" triggers on HABITATS_POLY

[HABITATS_POLY]

UPDATE:

[trigger's name: update_eunis22_codes]

When EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4 is updated then update EUNIS22_1, EUNIS22_2, EUNIS22_3, EUNIS22_4 where EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4 (HABITATS_POLY) = EUNIS22 (GESTION_CODES)

I will not list the spoilers in this post to make it clearer, but will add them in the discussion.

My issue is that it is not working when I insert several polygons. In HABITATS_POLY, the update of EUNIS22 will be done on one occurrence of each code, but not on all the codes. So, as soon as the corresponding EUNIS22 or a EUNIS code is given, then if the other codes are identical, they will not be found.

I'm working on it since weeks, there is always something wrong.

Here is a link to download an example project :Test Project


EDIT:

I am simplifying my question to address a specific issue that, once resolved, could help me understand the broader problem.

I want to update the fields EUNIS22_1, EUNIS22_2, EUNIS22_3, and EUNIS22_4 in the table HABITATS_POLY based on changes in the EUNIS22 field in the GESTION_CODE table. The current trigger is only updating one occurrence of each code.

When EUNIS22 is modified in the GESTION_CODES table, I aim to update all instances of codes EUNIS_1 (2, 3, or 4) in the HABITATS_POLY table where EUNIS_1 (2, 3, or 4) matches the EUNIS field in the GESTION_CODES table.

Ex: [EUNIS22_1 = EUNIS22 where EUNIS_1 = EUNIS]

    CREATE TRIGGER D_rapat_eunis_to_eunis22_updt
AFTER UPDATE OF EUNIS22 ON GESTION_CODES
BEGIN
    UPDATE HABITATS_POLY
    SET "EUNIS22_1" = NEW.EUNIS22
    WHERE "EUNIS_1" = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET "EUNIS22_2" = NEW.EUNIS22
    WHERE "EUNIS_2" = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET "EUNIS22_3" = NEW.EUNIS22
    WHERE "EUNIS_3" = NEW.EUNIS;

    UPDATE HABITATS_POLY
    SET "EUNIS22_4" = NEW.EUNIS22
    WHERE "EUNIS_4" = NEW.EUNIS;
    END;

EDIT2:

The solution I might have found:

CREATE TRIGGER update_eunis22_trigger
AFTER UPDATE OF EUNIS22 ON GESTION_CODES
BEGIN
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = CASE WHEN "EUNIS_1" IN (SELECT "EUNIS" FROM GESTION_CODES) THEN (SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1") ELSE "EUNIS22_1" END,
        "EUNIS22_2" = CASE WHEN "EUNIS_2" IN (SELECT "EUNIS" FROM GESTION_CODES) THEN (SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2") ELSE "EUNIS22_2" END,
        "EUNIS22_3" = CASE WHEN "EUNIS_3" IN (SELECT "EUNIS" FROM GESTION_CODES) THEN (SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3") ELSE "EUNIS22_3" END,
        "EUNIS22_4" = CASE WHEN "EUNIS_4" IN (SELECT "EUNIS" FROM GESTION_CODES) THEN (SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4") ELSE "EUNIS22_4" END;
END;

EDIT3:

It is working, but the triggers seems not to apply to the last feature added, when I copy-paste several features at the same time.

2
  • I might have found a solution, by changing the way values are searched. I think this solution is slower. See EDIT2
    – Giene
    Commented Dec 26, 2023 at 14:49
  • It is working, but the last row (or feature) is not updated when I copy-paste several features at the same time.
    – Giene
    Commented Dec 26, 2023 at 21:13

1 Answer 1

0

After carefully reanalyzing the process multiple times, I identified that the root cause of the issue stemmed from the order of trigger execution when the table is updated following a copy-paste operation. Specifically, the final row in HABITATS_POLY remained unaltered if the code in question had already undergone validation in the GESTION_CODES layer.

To address this, I implemented an additional trigger. The data undergoes scrutiny both after insertion and update in HABITATS_POLY and is reevaluated following an update in GESTION_CODES.

I improved a bit the code:

    CREATE TRIGGER G_rapat_eunis_to_eunis22_updt
AFTER UPDATE OF EUNIS22 ON GESTION_CODES
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = CASE WHEN "EUNIS_1" IS NOT NULL THEN COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1"), "-") END,
        "EUNIS22_2" = CASE WHEN "EUNIS_2" IS NOT NULL THEN COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2"), "-") END,
        "EUNIS22_3" = CASE WHEN "EUNIS_3" IS NOT NULL THEN COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3"), "-") END,
        "EUNIS22_4" = CASE WHEN "EUNIS_4" IS NOT NULL THEN COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4"), "-") END;
END;

But now, the process is slower.

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.