CODEWITHSUNDEEP
mysqltriggers
youwhatmate
youwhatmate

Reputation: 91

How to set a MySQL Trigger the correct way

i have a database table that gets updated via a CMS. Sometimes, there is one field that gets updated and then another field should get updated as well. I tried to get this done by just modifying the php code of the CMS, where the value gets saved in the DB. The problem is, with every update of the CMS this is gone.

I came across MySQL Triggers and i think this is a good way to do this. So whenever this field is updated, it should also update the other field with a value that will always be the same. But i cant quite get my head around the syntax, to get this going...

I tried:

CREATE TRIGGER after_unique_content_update
AFTER UPDATE
ON articles FOR EACH ROW
BEGIN
    IF OLD.date <> new.date THEN
        INSERT INTO release=2020
    END IF;
END

But it is not working. So what i try to do: Every time in the CMS someone changes the date, the release for that db entry should be set to 2020. The standard for release is NULL. With the above trigger, nothing happens after the date is changed... Never worked with Trigger or Events in MySQL before... How can i set the release to 2020 when the date field for that entry is updated?

Thanks for your help in advance!

best regards

Upvotes: 0

Views: 70

Answers (1)

Akina
Akina

Reputation: 42728

CREATE TRIGGER before_unique_content_update
BEFORE UPDATE
ON articles 
FOR EACH ROW
SET NEW.release = CASE WHEN OLD.date <> NEW.date
                       THEN 2020
                       ELSE NEW.release
                       END;

Firstly the condition is checked by the trigger, and, if the condition matched, new value to the column is set to 2020 (or, maybe, it's better to set it not to 2020 but to YEAR(CURRENT_DATE)?). If the condition not matched then the value is not altered (assigned value is the same as current one).

After the trigger performs this task, actual table data update is performed.

AFTER UPDATE trigger is not applicable - when it fires the data in the table is already set to the value specified in a query.

Upvotes: 1

Related Questions