One of the applications that I get to play with on a regular basis is a PHP, Javascript AJAX environment that acts as a frontend for a MySQL database. I wanted to add range constraints to some of the fields in order to reduce typo's.
I created before triggers with the following basic skeleton for each table that I wanted to add the range constraint to.
if NEW.columnname > theupperrange then
SELECT `**columnname is out of range**` from thetable into @str_val;
end if;
This will cause the insert to fail with the follow error message
Unknown column `**columnname is out of range**`
because of the nature of the application (if an error occurs in MySQL the PHP returns the error message) it then becomes the JavaScript's XMLHttprequest.responseText. so on the javascript side of the application i look for the Unknown column like this
//req is the name of the XMLHttpRequest object i create to handle interaction between
//the javascript and PHP.
if(req.responseTest.indexOf("Unknown column"))
{
msg = req.responseText
arr_msg = msg.split("**")
themsg = arr_msg[1]
alert(themsg)
//window1 is where data is entered, I know it's a real meaningful name:)
window1.focus()
}
else
{
regular application stuff
}
The idea of how to write the trigger was not original with me. I got it from http://www.brokenbuild.com/blog/category/mysql/ , who I believe said he got it from another source.
I created before triggers with the following basic skeleton for each table that I wanted to add the range constraint to.
if NEW.columnname > theupperrange then
SELECT `**columnname is out of range**` from thetable into @str_val;
end if;
This will cause the insert to fail with the follow error message
Unknown column `**columnname is out of range**`
because of the nature of the application (if an error occurs in MySQL the PHP returns the error message) it then becomes the JavaScript's XMLHttprequest.responseText. so on the javascript side of the application i look for the Unknown column like this
//req is the name of the XMLHttpRequest object i create to handle interaction between
//the javascript and PHP.
if(req.responseTest.indexOf("Unknown column"))
{
msg = req.responseText
arr_msg = msg.split("**")
themsg = arr_msg[1]
alert(themsg)
//window1 is where data is entered, I know it's a real meaningful name:)
window1.focus()
}
else
{
regular application stuff
}
The idea of how to write the trigger was not original with me. I got it from http://www.brokenbuild.com/blog/category/mysql/ , who I believe said he got it from another source.
Comments