Set variable if null in trigger

Question

I have a table for categories with "order" column for ordering the categories. I'm trying to set order incrementally in trigger before insert:

CREATE TRIGGER `categories_set_order` BEFORE INSERT ON `categories` FOR EACH ROW BEGIN
    DECLARE max_order INT DEFAULT 0;
    SELECT MAX(categories.order) INTO max_order FROM categories;
    SET NEW.order = max_order + 1;
END;

But if there are no records in the db, order column is being set to NULL. I've modified the trigger code:

CREATE TRIGGER `categories_set_order` BEFORE INSERT ON `categories` FOR EACH ROW BEGIN
    DECLARE max_order INT DEFAULT 0;
    SELECT MAX(categories.order) INTO max_order FROM categories;
    IF (ISNULL(@max_order)) THEN BEGIN
        SET max_order = 0;
    END;

    SET NEW.order = max_order + 1;
END;

And I'm getting the following error:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9

Line 9 is:

IF (ISNULL(@max_order)) THEN BEGIN

I've tried to remove the "@", but still getting the same error. How to fix it?

juergen d · Accepted Answer · 2014-01-18 15:29:06Z

6

You need to change the delimiter. Otherwise the DB thinks your trigger definition ends at the first ; which would be incomplete.

Also remove the BEGIN from your IF statement and add use END IF to end your IF

delimiter |
CREATE TRIGGER `categories_set_order` BEFORE INSERT ON `categories` 
FOR EACH ROW BEGIN
    DECLARE max_order INT DEFAULT 0;
    SELECT MAX(categories.order) INTO max_order FROM categories;
    IF (ISNULL(@max_order)) THEN
        SET max_order = 0;
    END IF;

    SET NEW.order = max_order + 1;
END
|
delimiter ;

edited Jan 18, 2014 at 15:29

answered Jan 18, 2014 at 15:15

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Comments

Mihai · Accepted Answer · 2014-01-18 15:17:17Z

2

Besides delimiter you need an END IF

DELIMITER // 
CREATE TRIGGER `categories_set_order` BEFORE INSERT ON `categories` FOR EACH ROW BEGIN
    DECLARE max_order INT DEFAULT 0;
    SELECT MAX(categories.order) INTO max_order FROM categories;
    IF (ISNULL(@max_order)) THEN 
        SET max_order = 0;
    END IF;   
    SET NEW.order = max_order + 1;
END; //
DELIMITER ;

answered Jan 18, 2014 at 15:17

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Set variable if null in trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related