Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Apr 2009
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default value determined by data in field?

    Hello,

    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?

    Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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?

    http://dev.mysql.com/doc/refman/5.1/...e-trigger.html

    Untested, of course, but your trigger might look like:
    Code:
     
    CREATE TRIGGER pinkblue AFTER INSERT ON yourtable
        UPDATE OLD.color = IF( OLD.sex = 'M', 'blue', 'pink' );

  • #3
    New Coder
    Join Date
    Apr 2009
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Last edited by yahooman123; 04-05-2009 at 10:20 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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:
    Code:
    CREATE TRIGGER pinkblue AFTER INSERT ON yourtable
        UPDATE OLD.color = IF( OLD.sex = 'M', 'blue', 'pink' )
        WHERE OLD.color IS NULL;
    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.

  • Users who have thanked Old Pedant for this post:

    yahooman123 (04-05-2009)

  • #5
    New Coder
    Join Date
    Apr 2009
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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.)

  • #8
    New Coder
    Join Date
    Apr 2009
    Posts
    10
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hmm, I don't know, I'll try looking around. Thanks for your help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •