View Full Version : Default value determined by data in field?

04-05-2009, 10:31 PM

I am wondering it is possible to have two default values set, and the default value that will be inserted is based on the data that is being inserted into one of the fields.

For example in a table there are 2 fields, "Sex" and "Color".

"Sex" is selected by php injection, whether it is male or female, and there are two default values set for the nulled "Color" field. One value is blue, the other is pink. So if "Male" is inserted into the field sex, then the default value that will be inserted into "Color" will be "Blue", and if "Female" is selected the default with be "Pink". Is this possible to do in MySQL? or would I have to manually inject both values?


Old Pedant
04-05-2009, 10:50 PM
You can create a TRIGGER to do this, but honestly I don't think it's worth the pain and trouble that triggers often cause later, when you are trying to figure out why something weird is happening. Since the logic is so so so easy to do in PHP, why not just do it there and be happy?


Untested, of course, but your trigger might look like:

UPDATE OLD.color = IF( OLD.sex = 'M', 'blue', 'pink' );

04-05-2009, 11:15 PM
Can you explain how the trigger may cause a problem later? Maybe it will not be a problem in my case, thanks!

The reason why I do not want it done with PHP is because there would be a lot of individual values that I would need to inject for both male/female. If the database can just do that automatically it would be so much easier

Old Pedant
04-05-2009, 11:23 PM
It's just that all too often people forget that there are triggers active and make changes in the queries that don't take that into account.

Just a simple example for you particular case: Say you added "androgynous" as another choice of gender (to "M" and "F"). As that trigger is written, all "A" gender records will be treated as "non-M" and get a "pink" color. And that is true EVEN if you inserted the color specifically as "purple". The TRIGGER occurs after the insert and changes the color no matter what.

Now, obviously a better trigger might be:

UPDATE OLD.color = IF( OLD.sex = 'M', 'blue', 'pink' )

so that an existing color value, specifically given in the INSERT, wouldn't get changed.

The better you are at writing triggers--the more experience you have with them--the more likely you are to not fall into traps like that one.

Note, too, that this trigger does *NOT* take care of UPDATE queries!! So if you wanted to have the colors match when an UPDATE occurs, you'd need to write a specific trigger for that, as well. (This is different than SQL Server triggers, where an UPDATE to a record is essentially treated as a DELETE followed by an UPDATE; at least MySQL is a bit more sane in this area.)

If you check out the various DB forums, that is ones created specifically for/by the DB experts, you'll see that most of them warn against proliferating triggers when they aren't really needed. Yes, there are places they are needed. I'm not convinced this is one of them.

04-05-2009, 11:31 PM
Hey I tried to insert this trigger in SQL query and I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE OLD.color = IF( OLD.sex = 'M', 'blue', 'pink' )
WHERE OLD.color IS N' at line 2

Old Pedant
04-05-2009, 11:44 PM
Ugh... Dunno off top of my head. It *looks* right to me. I've used a grand total of maybe 2 MySQL triggers in my life. Mostly a SQL Server person.

Play with it a bit? Try variations? If you can't get it, I'd go post in the MySQL forums at www.MySQL.com

Oh...what version of MySQL are you using?? That code is pretty much from the 5.1 docs.

Old Pedant
04-05-2009, 11:45 PM
Maybe try NEW in place of OLD???

Maybe OLD only applies to UPDATE triggers?? (The only triggers I created happened to indeed be for UPDATE queries.)

04-06-2009, 12:04 AM
Hmm, I don't know, I'll try looking around. Thanks for your help.