Re: trigger not working on same database on two versions of mysql

[Peter Brawley]

Using this script ...

set @old_sql_mode=@@sql_mode;
set sql_mode="";
drop table if exists goods;
CREATE TABLE goods (
  id int unsigned PRIMARY KEY AUTO_INCREMENT,
  quantity int NOT NULL DEFAULT 0
) ENGINE=InnoDB;

DELIMITER |
CREATE TRIGGER `negative_insert`
BEFORE INSERT ON `goods`
FOR EACH ROW BEGIN
  IF NEW.`quantity` < 0 THEN
    SET NEW.`quantity` = 1 / 0;
  END IF;
END;
|
CREATE TRIGGER `negative_update`
BEFORE UPDATE ON `goods`
FOR EACH ROW BEGIN
  IF NEW.`quantity` < 0 THEN
    SET NEW.`quantity` = 1 / 0;
  END IF;
END;
|
DELIMITER ; 

INSERT INTO goods (quantity) values (-1); 
insert into goods values(1,10);
update goods set quantity =-1 where id=1; 

set @@sql_mode=@old_sql_mode;

I can't get your trigger to fail on 5.0, 5.1, 5.5 or 6.0. If you find a setting that makes it fail, please let us know.

PB