MySQL trigger with conditions on UPDATE

Question

Is it possible to insert a conditional type of update in a mysql trigger? If I need to do something after a certain type of update, for example if the update is a certain type of changing in field value. Better said, if I need to increment a value in another table after that a specific field in my original table changes his value to a specific value, is it possible? Any help will be greatly appreciated.
I'm trying this:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
BEGIN
UPDATE USER
SET num_sub=num_sub+1
IF NEW.State <> OLD.State AND NEW.STATE='C'
END IF
END

But it doesn't work, syntax error. I insert the trigger via phpmyadmin.

yes possible, so in other words when you run a trigger on certain table operation you can always do insert/update/delete on a different table provided that table does not have a trigger doing some DML on first table. Provide some sample example of what you are trying to do. — Abhik Chakraborty, Sep 24 '17 at 16:58
`IF NEW.column1 <> OLD.column1 AND NEW.column1 = 'specific value' UPDATE table2 ...`. You should post the CREATE statements for both tables and be more specific in your question, if you want a good answer. — Paul Spiegel, Sep 24 '17 at 17:03
Possible duplicate of [MySQL trigger On Insert/Update events](https://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events) — cwallenpoole, Sep 24 '17 at 17:34
@PaulSpiegel I've edited my initial message. I'm trying your suggestion but I'm unable to make it work. Sorry I have zero experience with triggers. — JamieITGirl, Sep 24 '17 at 17:56

Paul Spiegel · Accepted Answer · 2017-09-24T18:54:23.687

4

Try something like this:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
BEGIN
    IF NEW.State <> OLD.State AND NEW.STATE='C' THEN
        UPDATE USER
        SET num_sub=num_sub+1
        WHERE USER.id = NEW.user_id; -- change this condition as you need
    END IF;
END

This should also work:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
    UPDATE USER
    SET num_sub=num_sub+1
    WHERE USER.id = NEW.user_id
      AND NEW.State <> OLD.State
      AND NEW.STATE='C';

Note: NEW and OLD refer to the row from the Subscription table which is being updated. OLD contains the values before the update. NEW contains the values after the update. So to check if the state has been changed you can compare NEW.State <> OLD.State.

edited Sep 24 '17 at 18:54

answered Sep 24 '17 at 18:14

Paul Spiegel

30,925
5
44
53

USER.id must be the USER.id of the USER table or the SUBSCRIPTION table? (obviously there is a FK on subscription.userS which points to user.id). Thanks!! – JamieITGirl Sep 24 '17 at 18:39
1

It was just a quess, since you didn't provide the schema and the relation. Now change that line to `WHERE USER.id = NEW.userS`. BTW: `USER.id` means column `id` from `USER` table. `NEW.userS` is the column `userS` from the row which you have just updated in the `Subscription` table. – Paul Spiegel Sep 24 '17 at 18:44
I just tried but it does nothing on the USER table. Maybe it's because Subscription.UserS points to User.id but it's not the primary key of the subscription table? If that's so, how can I resolve? – JamieITGirl Sep 24 '17 at 18:59
1

Well.. Provide CREATE statements for both tables and INSERT statements with sample data so I can test the trigger. – Paul Spiegel Sep 24 '17 at 19:03
That was my mistake; the field num_sub was initialized to NULL. It seems to work fine. Thanks a lot. – JamieITGirl Sep 24 '17 at 19:07
1

@JamieITGirl you can also change the line to `num_sub = coalesce(num_sub, 0) + 1`. This way `NULL` wil be "converted" to `0`. – Paul Spiegel Sep 24 '17 at 19:12

MySQL trigger with conditions on UPDATE

1 Answers1