...

View Full Version : Create Trigger Question



Aradon
07-20-2006, 03:39 PM
*************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!

Aradon
07-20-2006, 04:40 PM
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.

eak
07-20-2006, 05:10 PM
You may want to read this article.
http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum