Clever Geek Handbook

MySQL Trigger doesn't know default values

I am using this trigger

delimiter ||
create TRIGGER column_a_to_default 
BEFORE INSERT ON `property`
FOR EACH ROW
BEGIN  
  IF NEW.primary_image = '' THEN
    SET NEW.primary_image = default(NEW.primary_image);
  END IF;
END;
||
delimiter ;

If I insert into a table, the trigger throws an error:

The 'primary_image' field has no default.

But it is so!

What's wrong here? The trigger doesn't seem to know the default values!

EDIT

Create script table

CREATE TABLE IF NOT EXISTS `property` (
  `id` varchar(10) NOT NULL,
  `images` text NOT NULL,
  `primary_image` varchar(100) NOT NULL DEFAULT '../no-image.png',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+3


source to share


2 answers


Doesn't work default(property.primary_image)

?



(Get the default for the Real table, not NEW hold-table)

+3


source


Somehow it doesn't work.

Try a workaround -



  IF NEW.primary_image = '' THEN
    SELECT COLUMN_DEFAULT INTO @def
      FROM information_schema.COLUMNS
    WHERE
      table_schema = 'database_name'
      AND table_name = 'property'
      AND column_name = 'primary_image';
    SET NEW.primary_image = @def;
  END IF;
+1


source






More articles:


All Articles