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?

Answer: 
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 ;**

```
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 ;

```