CODEWITHSUNDEEP
mysqlsqltriggersdefault-value
juergen d
juergen d

Reputation: 204864

MySQL Trigger doesn't know default value

I use 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 the table the trigger throws an error:

Field 'primary_image' doesn't have a default value.

But it does!

What is wrong here? It seems like the trigger isn't aware of default values!

EDIT

Table Create script

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;

Upvotes: 4

Views: 1083

Answers (2)

MatBailie
MatBailie

Reputation: 86765

Doesn't default(property.primary_image) work?

(Getting the default of the Real table, not the NEW holding-table)

Upvotes: 4

Devart
Devart

Reputation: 122002

Somehow it does not work.

Try this 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;

Upvotes: 1

Related Questions