Hi!
>>>>> "Federico" == Federico Razzoli <[email protected]> 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
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
