SQL trigger not working in MySQL database

Question

so I'm try to create auto increment by using trigger with case the case works when the parameter insert but the value is not auto increment and stay "001" here is the code

DELIMITER $$

USE `onlinestore`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trigger_sensors`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trigger_sensors` BEFORE INSERT ON `sensors` 
    FOR EACH ROW 
BEGIN
  IF (NEW.label IS NULL) THEN
    -- Find max existed label for specified sensor type
    SELECT
      MAX(label) INTO @max_label
    FROM
      sensors
    WHERE
      TYPE = NEW.name;

    IF (@max_label IS NULL) THEN
      SET @label =
        CASE NEW.name
        WHEN 'VIP' THEN 'VIP'
        WHEN 'REGULER' THEN 'R'
        WHEN 'MROOM' THEN 'MR'
        ELSE 'UNKNOWN'
      END;

      -- Set first sensor label
      SET NEW.label = CONCAT(@label, '001');
    ELSE
      -- Set next sensor label
      SET NEW.label = CONCAT(SUBSTR(@max_label, 1, 2), LPAD(SUBSTR(@max_label, 3) + 1, 4, '0'));
    END IF;
  END IF;
END;
$$

DELIMITER ;

here is the value in database

i cannot find where did I does wrong.... can someone help me?

Quintria · Accepted Answer · 2021-07-05 19:05:18Z

1

In the first SELECT query you have a mistake in the WHERE clause. It should be WHERE NAME = NEW.name;

answered Jul 5, 2021 at 19:05

Quintria

364 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JS24 Over a year ago

thnks, i wasn't pay attention at that

Collectives™ on Stack Overflow

SQL trigger not working in MySQL database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related