Re: [Maria-discuss] Reading a field's default value from a trigger

Hi!

...

...

...

...

"Federico" == Federico Razzoli writes:

Federico> I'm not sure if I use a wrong syntax or I found a bug. But I can't read a column's default value from a trigger. Federico> The trigger just checks if the INSERT is trying to set a column to its default value: Federico> DELIMITER || Federico> CREATE TABLE `blog` Federico> ( Federico> `created_by` CHAR(60) NOT NULL DEFAULT '' Federico> ); Federico> CREATE TRIGGER `blog_default_created_by` Federico> BEFORE INSERT Federico> ON `blog` Federico> FOR EACH ROW Federico> BEGIN Federico> IF NEW.`created_by` = DEFAULT(NEW.`created_by`) THEN Federico> SET NEW.`created_by` = USER(); Federico> END IF; Federico> END; Federico> || Federico> DELIMITER ; Federico> But when I try to INSERT a row I get an error: Federico> MariaDB [test]> INSERT INTO `blog` SET `created_by` = ''; Federico> ERROR 1364 (HY000): Field 'created_by' doesn't have a default value Federico> This problem disappears if I replace DEFAULT(NEW.`created_by`) with '', so I'm not in troubles - but in theory a default value may change, so using DEFAULT() is a better practice. Federico> The syntax I'm using could be wrong, but I've tried all the alternatives which seemed to me possible, and they didn't work: DEFAULT, DEFAULT(`created_by`), DEFAULT('created_by'). This is a bug in how MariaDB / MySQL detects if a field has a default value. It's probably because of this is a trigger on 'new', which is not something that is apparently not handled properly. Do you want to try to fix it yourself? (Shouldn't take many minutes ;) Regards, Monty