Create SQL trigger to update row value

Question

I have a SQL table "Inbox" with column "modem_id" and "user" I would like to create a SQL trigger when the modem_id is 1, the "user" column is being inserted value 'A'; 2 for user 'B'...

I have created a SQL trigger with if else:

delimiter //
CREATE TRIGGER upd_user_inbox BEFORE insert ON inbox
    FOR EACH ROW
    BEGIN
    IF NEW.modem_id = 0 THEN
             UPDATE `inbox` SET `user` = 'A' WHERE modem_id = 1; 
         ELSEIF NEW.modem_id = 2 THEN
             UPDATE `inbox` SET `user` = 'B' WHERE modem_id = 2; 
            ELSEIF NEW.modem_id = 3 THEN
             UPDATE `inbox` SET `user` = 'C' WHERE modem_id = 3; 
            ELSEIF NEW.modem_id = 4 THEN
             UPDATE `inbox` SET `user` = 'D' WHERE modem_id = 4; 
            ELSEIF NEW.modem_id = 5 THEN
             UPDATE `inbox` SET `user` = 'E' WHERE modem_id = 5; 
            ELSEIF NEW.modem_id = 6 THEN
             UPDATE `inbox` SET `user` = 'F' WHERE modem_id = 6; 
            ELSEIF NEW.modem_id = 7 THEN
             UPDATE `inbox` SET `user` = 'G' WHERE modem_id = 7; 
            ELSEIF NEW.modem_id = 8 THEN
             UPDATE `inbox` SET `user` = 'H' WHERE modem_id = 8; 
         END IF;
     END;//
delimiter ;

The trigger execute successfully but it is not working. If 'if-else' does not work, anyone could teach me using 'case' instead?

Thank you

You can't modify a table in a trigger which was invoked by modifying the same table, q.v. [here](http://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert). — Tim Biegeleisen, Aug 04 '16 at 00:36
You might be able to handle this by writing a stored procedure. — Tim Biegeleisen, Aug 04 '16 at 00:41
Do you want to set the user column in the current row you are inserting to 'A' , 'B' etc? If so, don't use an update, just set the `NEW.user = 'A'` etc. — Ryan Vincent, Aug 04 '16 at 00:47
@RyanVincent yeah it works by using : IF NEW.modem_id = 1 THEN SET NEW.user = 'A'; Thanks — Kai Tzer, Aug 04 '16 at 00:51

score 0 · Answer 1 · answered Aug 04 '16 at 01:01

0

https://dev.mysql.com/doc/refman/5.7/en/case.html

CASE NEW.modem_id
WHEN 0 THEN ...
WHEN 2 THEN ...
...
END CASE

answered Aug 04 '16 at 01:01

SIDU

2,258
1
12
23

Create SQL trigger to update row value

1 Answers1