mysql where clause error in trigger

Question

I've added triggers between user TABLE and school TABLE, for student-transferring school and student-adding in school, the numbers_of_school field is where problem lies maybe.

The error msg is:

#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 'WHERE NEW.NUMBERS_OF_STUDENT < 0; END' at line 4

CREATE TABLE IF NOT EXISTS `school` (
  `school_id` int(11) NOT NULL,
  `school_name` varchar(45) NOT NULL,
  `location` varchar(45) NOT NULL,
  `master` varchar(45) NOT NULL,
  `numbers_of_student` int(11) NOT NULL,
  PRIMARY KEY (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- TABLE `school`
--
DROP TRIGGER IF EXISTS `tg_school_insert`; 
DELIMITER //
CREATE TRIGGER `tg_school_insert` BEFORE INSERT ON `school`
 FOR EACH ROW BEGIN
    IF NEW.NUMBERS_OF_STUDENT = NULL THEN
        SET NEW.NUMBERS_OF_STUDENT = 0;
    END IF;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `tg_school_delete`; 
DELIMITER //
CREATE TRIGGER `tg_school_delete` AFTER DELETE ON `school`
 FOR EACH ROW BEGIN
DELETE FROM DEPARTMENT
WHERE SCHOOL_ID=OLD.SCHOOL_ID;
UPDATE USER
SET SCHOOL_ID = NULL
WHERE SCHOOL_ID = OLD.SCHOOL_ID;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `tg_school_update`; 
DELIMITER //
CREATE TRIGGER `tg_school_update` BEFORE UPDATE ON `school`
 FOR EACH ROW BEGIN
SET NEW.NUMBERS_OF_STUDENT = 0
WHERE NEW.NUMBERS_OF_STUDENT < 0;
END
//
DELIMITER ;

and my user table below:

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) DEFAULT NULL,
  `school_id` int(11) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `realname` varchar(45) DEFAULT NULL,
  `familyname` varchar(45) DEFAULT NULL,
  `birthdate` varchar(45) DEFAULT NULL,
  `gender` tinyint(1) NOT NULL,
  `photo` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `school_enteryear` int(11) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `activestat` varchar(45) DEFAULT NULL,
  `onlinestat` varchar(45) DEFAULT NULL,
  `regtime` datetime NOT NULL,
  `avatar` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `desc` varchar(45) DEFAULT NULL,
  `self_comment` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `fk_user_department_id_idx` (`department_id`),
  KEY `fk_user_school_id_idx` (`school_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- TRIGGER `user`
--
DROP TRIGGER IF EXISTS `tg_user_delete`; 
DELIMITER //
CREATE TRIGGER `tg_user_delete` AFTER DELETE ON `user`
 FOR EACH ROW BEGIN
DELETE FROM HOBBY WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_ALBUM_PHOTO WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_DIARY WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_MSG WHERE FROM_ID = OLD.USER_ID OR TO_ID = OLD.USER_ID;
DELETE FROM USER_NOTIFICATION WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_OP WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_FEED WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_RELATION WHERE USER_ID = OLD.USER_ID OR USER2_ID = OLD.USER_ID;
DELETE FROM USER_SPECIAL WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_STATUS WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_DIARY_COMMENT WHERE USER_ID = OLD.USER_ID;
DELETE FROM USER_STATUS_COMMENT WHERE USER_ID = OLD.USER_ID;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `tg_user_bf_insert`;
DELIMITER //
CREATE TRIGGER `tg_user_bf_insert` BEFORE INSERT ON `user`
 FOR EACH ROW BEGIN
    IF NEW.regtime = '0000-00-00 00:00:00' THEN
        SET NEW.regtime = NOW();
    END IF;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `tg_user_insert`;
DELIMITER //
CREATE TRIGGER `tg_user_insert` AFTER INSERT ON `user`
 FOR EACH ROW BEGIN
UPDATE SCHOOL SET NUMBERS_OF_STUDENT=NUMBERS_OF_STUDENT+1
WHERE SCHOOL_ID = NEW.SCHOOL_ID;
INSERT INTO HOBBY(`user_id`) VALUES(NEW.USER_ID);
END
//
DELIMITER ;


DROP TRIGGER IF EXISTS `tg_user_update_school`;
DELIMITER //
CREATE TRIGGER `tg_user_update_school` AFTER UPDATE ON `user`
 FOR EACH ROW BEGIN
UPDATE SCHOOL
SET NUMBERS_OF_STUDENT=NUMBERS_OF_STUDENT-1
WHERE OLD.SCHOOL_ID <> NEW.SCHOOL_ID AND SCHOOL_ID = OLD.SCHOOL_ID;
UPDATE SCHOOL
SET NUMBERS_OF_STUDENT=NUMBERS_OF_STUDENT+1
WHERE OLD.SCHOOL_ID <> NEW.SCHOOL_ID AND SCHOOL_ID = NEW.SCHOOL_ID;
END
//
DELIMITER ;

But where is the syntax error? thank you!

Answer 1

As per my comment: Try this out instead of the current code you use.

SET NEW.NUMBERS_OF_STUDENT = 
( CASE WHEN NEW.NUMBERS_OF_STUDENT < 0 THEN 0 
  ELSE NEW.NUMBERS_OF_STUDENT 
END )

Answer 2

CREATE TRIGGER `tg_school_update` BEFORE UPDATE ON `school`
 FOR EACH ROW BEGIN
   IF NEW.NUMBERS_OF_STUDENT < 0
   THEN SET NEW.NUMBERS_OF_STUDENT = 0;
   END IF;
END;

Answer 3

Change this:

SET NEW.NUMBERS_OF_STUDENT = 0
WHERE NEW.NUMBERS_OF_STUDENT < 0;

To this:

SET NEW.NUMBERS_OF_STUDENT 
    = GREATEST(NEW.NUMBERS_OF_STUDENT, 0);

You also have a bug. This line:

IF NEW.NUMBERS_OF_STUDENT = NULL THEN

Should be:

IF NEW.NUMBERS_OF_STUDENT IS NULL THEN

the comparison of column = null is never true - you must use the special is null syntax to test for null.

Answer 4

I am posting this as an answer because it is too long for a comment. I just ran all of your scripts and the error is with this piece of code:

DROP TRIGGER IF EXISTS `tg_school_update`; 
DELIMITER //
CREATE TRIGGER `tg_school_update` BEFORE UPDATE ON `school`
 FOR EACH ROW BEGIN
SET NEW.NUMBERS_OF_STUDENT = 0
WHERE NEW.NUMBERS_OF_STUDENT < 0;
END
//
DELIMITER ;

It doesn't like the line:

WHERE NEW.NUMBERS_OF_STUDENT < 0;

If you remove this the trigger is created. So my question is, is it possible to negative number of students?

Or change the code to:

DELIMITER //
CREATE TRIGGER `tg_school_update` BEFORE UPDATE ON `school`
 FOR EACH ROW BEGIN
SET NEW.NUMBERS_OF_STUDENT = case when NEW.NUMBERS_OF_STUDENT < 0 then 0 else NEW.NUMBERS_OF_STUDENT end;
END
//
DELIMITER ;