SOLVED (somehow...): trigger not working on same database on two versions of mysql

[Liviu Vasut]

I ran the script on my station (mysql 5.1.52) and it raises the error both on insert and update. On the production server (mysql 5.0.88) it only complains on insert. I also tried with a sql_mode="traditional" as documentation suggests but with no effect...
I will, of course, post a solution if I'll find one :)

-- edit 10 minutes later
I redefined the quantity field as unsigned, dropped the triggers and used a traditional sql mode. This combination does the job both on insert and update and on both my server versions.

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

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

set @@sql_mode=@old_sql_mode;

Thanks Peter for your time.



Edited 1 time(s). Last edit at 12/11/2010 12:56AM by Liviu Vasut.