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 3 of 3
  1. #1
    Regular Coder Aradon's Avatar
    Join Date
    Jun 2005
    Location
    USA
    Posts
    734
    Thanks
    0
    Thanked 20 Times in 19 Posts

    Create Trigger Question

    *************SOLVED, see second post******************

    Hello all,

    I've recently just installed mysql on my machine and am slowly learning the ins and outs. I took one database class a long time ago so excuse me for my newbie-ish questions.

    I have two tables. One of those tables has a date field that stores a date. I was wondering what type of constraints I could put on this field? Can I somehow get mysql to reject any dates that are before the current date?

    In thinking ahead, I thought I might be able to do this with a trigger, however I don't know if a trigger can stop an insert or not. If so, how do you get a trigger to stop an insert. Like if it doesn't meet a constraint in an if statement?

    Thanks for your help everyone!
    Last edited by Aradon; 07-20-2006 at 05:41 PM.

  • #2
    Regular Coder Aradon's Avatar
    Join Date
    Jun 2005
    Location
    USA
    Posts
    734
    Thanks
    0
    Thanked 20 Times in 19 Posts
    Okay, well I figured it out.

    Basically (for those who are reading this and are as new to sql as I am) a trigger can change a value however it cannot prevent an insert or create or delete from occuring. Instead you can do one of two things

    The first possibility is that you change the date to a date that has a previous year and use a stored procedure to clean it out every night

    Or, what my oracle guru suggested to me was that if I didn't want to do it on the higher level (through my java application) I could create a constraint on the column that made the date after a certain date (that date perhaps being the day before).

    I'll probably try out the second option before just putting it on my higher level application. But it was worth a question.

  • #3
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    You may want to read this article.
    http://dev.mysql.com/tech-resources/...eign-keys.html
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."


  •  

    Posting Permissions

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