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!

Can you post the error you are getting? Errors say alot more info than just bugging us ;) — bonCodigo
– bonCodigo, Commented Dec 21, 2012 at 20:36
more info? Sorry, but I don't exactly get your meanings. @bonCodigo — Flora Mitchell
– Flora Mitchell, Commented Dec 21, 2012 at 20:38
@bonCodigo The error message is the big bold bit of text at the top of the question ;) — Clive
– Clive, Commented Dec 21, 2012 at 20:39
@Clive thx, before you answered I suspected that if there would be more error info that I didn't notice. — Flora Mitchell
– Flora Mitchell, Commented Dec 21, 2012 at 20:41
I would just use a case : you could try. SET NEW.NUMBERS_OF_STUDENT = ( CASE WHEN NEW.NUMBERS_OF_STUDENT < 0 THEN 0 NEW.NUMBERS_OF_STUDENT END ) — bonCodigo
– bonCodigo, Commented Dec 21, 2012 at 20:49

Taryn · Accepted Answer · 2012-12-21 20:48:25Z

1

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 ;

answered Dec 21, 2012 at 20:48

Taryn

249k57 gold badges375 silver badges409 bronze badges

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

4 Comments

Flora Mitchell Over a year ago

thanks for answer. I think It is possible to negative number of students for the trigger in user table can set substract the number of students when a student is seemed as transferring.

bonCodigo Over a year ago

@Bluefeet I suspected the same, but OP said he is just preventing from having a negative number ... :$

Taryn Over a year ago

@SummerR.L see my edit, I got it to create the trigger with the case

Flora Mitchell Over a year ago

all right, I think you're right. Oh my. when a school is with 0 number of students, no student can transferred to other school from it. Thank you!

Bohemian · Accepted Answer · 2012-12-21 20:52:45Z

1

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.

answered Dec 21, 2012 at 20:52

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

1 Comment

Bohemian Over a year ago

It's not valid syntax. Even though a human would understand what you coded, WHERE is not expected here in SQL. The code I changed it to expresses your intention in the minimum amount of code.

Barmar · Accepted Answer · 2012-12-21 20:57:53Z

1

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;

answered Dec 21, 2012 at 20:57

Barmar

789k57 gold badges555 silver badges669 bronze badges

7 Comments

Flora Mitchell Over a year ago

is it IF clause sth. special? Yesterday, when I added trigger on set regtime = now(); I failed at the very beginning without IF clause also.

Barmar Over a year ago

No, there's nothing special about IF, it's just another kind of statement. The problem with set regtime = now(); is that user variables need to begin with @, so it should be set @regtime = now();. See the documentation

Flora Mitchell Over a year ago

user variables, you mean the user is protection word so its variables is special? @Barmar

Barmar Over a year ago

No, I mean user-defined variables have to begin with @. See the documentation. Names without this prefix are assumed to be table column names.

Flora Mitchell Over a year ago

but my regtime is a column in user TABLE in deed. @Barmar

|

bonCodigo · Accepted Answer · 2012-12-21 21:11:43Z

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 )

edited Dec 21, 2012 at 21:11

answered Dec 21, 2012 at 20:52

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

6 Comments

Flora Mitchell Over a year ago

So, may I know why where clause failed? the difference between where clause and when clause with case? :)

bonCodigo Over a year ago

@SummerR.L my answer has case too :( why only mine got outcast?

Flora Mitchell Over a year ago

Sorry, I just firstly sawed his answer. although your syntax is clearer.

Flora Mitchell Over a year ago

and I tried it, it didn't work. :( maybe you lost ELSE after THEN 0.

bonCodigo Over a year ago

@SummerR.L the way you use where clause didn't look right. Usually for such validations I depend on case so I thought that's what would need here. But I am more than happy to hear the exact great reason :) Yeah preceisely I have lost else.

|

Collectives™ on Stack Overflow

mysql where clause error in trigger

4 Answers 4

4 Comments

1 Comment

7 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

7 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related