0

Currently, I have two Tables:

ocok_country

status country_id
1 1
1 2
0 3
0 4

** country_id 3 and 4 not updated with the trigger **

ocok_zone_to_geo_zone

name country_id
ABC 1
DEF 2
new-country-zone 3
new-country-zone 4

I have created a query that will Update the table ocok_county.status to 1 IF the ocok_zone_to_geo_zone.country_id Exist - see Country_id 1 and 2 on the tables

UPDATE ocok_country c
SET c.status = CASE
        WHEN EXISTS (
            SELECT 1
            FROM ocok_zone_to_geo_zone s
            WHERE s.country_id = c.country_id
        ) THEN '1'
        ELSE '0'
    END;

Now, I have also tried to create a trigger that will do this and save me some time but, I cannot find where is the error and I'll appreciate your help on this one Thanks! See country_id 3 and 4 not updated in status

CREATE TRIGGER update_ocok_zone_trigger AFTER UPDATE
ON
    `ocok_country` FOR EACH ROW
BEGIN
    IF EXISTS
        (
        SELECT
            1
        FROM
            `ocok_zone_to_geo_zone`
        WHERE
            `country_id` = NEW.country_id
    ) THEN
UPDATE
    `ocok_country`
SET
    `status` = '1'
WHERE
    `country_id` = NEW.country_id;
END IF;
END;

Currently using mysql 8.0.32

1 Answer 1

1
CREATE TRIGGER tr
AFTER INSERT ON ocok_zone_to_geo_zone
FOR EACH ROW
    UPDATE ocok_country
    SET status = 1
    WHERE country_id = NEW.country_id;
2
  • Thanks for your support, I also forgot to ask, what if I remove a country row from the table ocok_zone_to_geo_zone, now that such country_id doesn't exist in the table, this should disable the ocok_country.status = 0 in a way to make it dynamic and not create multiple triggers Feb 11, 2023 at 22:16
  • 1
    @carloslozada what if I remove a country row from the table ocok_zone_to_geo_zone Of course you must create according AFTER UPDATE and AFTER DELETE triggers.
    – Akina
    Feb 12, 2023 at 10:21

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.