BLOCKING BLANK VALUE ENTRY INTO A DATABASE

jayprakash sharma

Total Post:117

Points:821
Posted by  jayprakash sharma
 1167  View(s)
Ratings:
Rate this:
Hi Everyone!

I have a database into which i enter values through an application. To be specific ,i enter certain value in a text-box and click on save Button which handles the task of saving it into the database. The problem that i'm facing is even when i don't enter any value in the text-box and click on save button the value gets saved into the database( A case which i should have handled in the code ). Now i want to write a SQL-script so that it blocks the entry of the blank values into the database .

for example lets say ..books be the table which contains columns author_name , price , year

so if a try to enter a blank author name and try saving it, it should not be saved

I tried this:

 DELIMITER |

 CREATE TRIGGER test AFTER INSERT ON books
  FOR EACH ROW BEGIN
  IF NEW.author_name= '' THEN    
  delete from books where author_name= '';
  END IF;      
END;
|books
DELIMITER ;
can anyone tell me how to do this , is there any other method to achieve this ?

Thanks in advance! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Blocking blank value entry into a database

    Hi Jayprakash!

    Your trigger should look something like this:

    CREATE TRIGGER <name of the trigger> BEFORE INSERT ON <name of table>
        FOR EACH ROW BEGIN
          IF NEW.<fieldname> = ''
          THEN
            SET NEW='Error: Cannot insert record empty value for <fieldname>';
          END IF;
        END
        ;
    Create the trigger BEFORE insert

    OR

    You should validate user input in the application.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!