1

I was looking at creating a TRIGGER that will set the value of a column to its DEFAULT if the INSERT value happens to be an empty string.

My TRIGGER looks like this:

CREATE TRIGGER column_a_to_default 
BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.a = '' THEN
SET NEW.a = 'some value';
END IF;
END

I would like to know if I can replace 'some value' with a way to set it to the DEFAULT value of the column. i.e.

SET NEW.a = a.DEFAULT

Thanks

hungerstar
  • 21,206
  • 6
  • 50
  • 59

1 Answers1

4

This should work for you

SET NEW.a = DEFAULT(NEW.a)

EDIT: It looks like that doesn't work.

Use this workaround 

IF NEW.a = '' THEN
   SELECT COLUMN_DEFAULT INTO @def
   FROM information_schema.COLUMNS
   WHERE
     table_schema = 'database_name'
     AND table_name = 'your_table'
     AND column_name = 'a';
   SET NEW.a = @def;
END IF;

You can also try 

SET NEW.a = DEFAULT(table_name.a)
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Wow, I searched around and couldn't find anything and pseudo code was really close. Thanks! – hungerstar Mar 22 '12 at 06:58
  • Actually, this is creating a warning of "Column 'a' cannot be null" if the supplied value matches the IF statement in the TRIGGER. – hungerstar Mar 22 '12 at 07:25
  • 1
    Or better `DEFAULT(a)`. See the documentation: MySQL's [`DEFAULT()`](http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_default) function. – ypercubeᵀᴹ Mar 22 '12 at 07:27
  • I'm getting a new error "Unknown column 'a' in 'field list'". I'm checking for an empty string and want to supply the column's DEFAULT. My INSERT looks like this: `INSERT INTO table ( a, b, c ) VALUES ( '', 'two', 'three' )` – hungerstar Mar 22 '12 at 07:41
  • I am sorry I forgot the `NEW.` at the `a`. See my update and try again. – juergen d Mar 22 '12 at 07:50
  • I just tried that. I now get and error "Field 'a' doesn't have a default value". But it does, I double checked. Thanks for the help. – hungerstar Mar 22 '12 at 07:53
  • You can provide your table structure and trigger on [SQLFiddle](http://sqlfiddle.com/)? – juergen d Mar 22 '12 at 07:58
  • Having trouble building the schema for the trigger – hungerstar Mar 22 '12 at 08:10
  • Did you change the Server version in the combobox in the upper left corner to MySQL? Post the link and I will have a look. – juergen d Mar 22 '12 at 08:11
  • @hungerstar: I re-asked your question in a more specific way. See here for the solution: [mysql-trigger-doesnt-know-default-value](http://stackoverflow.com/questions/9818956/) – juergen d Mar 22 '12 at 09:15
  • @hungerstar: I updated it. Could you also try `SET NEW.a = DEFAULT(table_name.a)`? I think that works. – juergen d Mar 22 '12 at 16:13
  • Using `SET NEW.a = DEFAULT(table_name.a)` creates an error "Unknown table 'table_name' in field list". The solution with `SELECT COLUMN_DEFAULT INTO @def` in it works. Thanks! – hungerstar Mar 22 '12 at 19:16