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

18 Jan 2013

      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.

The trigger just checks if the INSERT is trying to set a column to its default value:

DELIMITER ||

CREATE TABLE `blog`
(
	`created_by` CHAR(60) NOT NULL DEFAULT ''
);

CREATE TRIGGER `blog_default_created_by`
	BEFORE INSERT
	ON `blog`
	FOR EACH ROW
BEGIN
	IF NEW.`created_by` = DEFAULT(NEW.`created_by`) THEN
		SET NEW.`created_by` = USER();
	END IF;
END;

||
DELIMITER ;

But when I try to INSERT a row I get an error:

MariaDB [test]> INSERT INTO `blog` SET `created_by` = '';
ERROR 1364 (HY000): Field 'created_by' doesn't have a default value

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.

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').

However I may still miss something, so I wanted to ask to the list before reporting this in JIRA.

Federico Razzoli