MySQL Trigger doesn't know default value

Question

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;

Doesn't default(property.primary_image) work? (Getting the default of the Real table, not the NEW holding-table) — MatBailie
– MatBailie, Commented Mar 22, 2012 at 9:20
@Dems: You are right. It does work. Post it as answer please. — juergen d
– juergen d, Commented Mar 22, 2012 at 16:17

MatBailie · Accepted Answer · 2012-03-22 16:18:33Z

4

Doesn't default(property.primary_image) work?

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

answered Mar 22, 2012 at 16:18

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devart Over a year ago

Really. It works. I had tried this too, but somehow it did not work for me. Anyway +1.

MatBailie Over a year ago

@Devart - I think you guys were trying default(NEW.primary_image), but the NEW table isn't that property table. It's just a similar table, with only what is necessary to log the changes. Querying property directly ensures you get what you want.

Devart · Accepted Answer · 2012-03-22 09:06:36Z

1

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;

answered Mar 22, 2012 at 9:06

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

juergen d Over a year ago

Unfortunately it doesn't work. Error: Column primary_image cannot be null. Seems like it can't get the default value this way either.

Devart Over a year ago

INSERT INTO property (primary_image) VALUES (''); should work

juergen d Over a year ago

I am sorry. It worked. I forgot to replace the database_name string. Thank you very much!

Collectives™ on Stack Overflow

MySQL Trigger doesn't know default value

EDIT

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related