Reputation: 281

Blocking blank value entry into a database

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 ?

Upvotes: 0

Answers (5)

friol

Reputation: 7096

What you want is called "constraint":

ALTER TABLE books MODIFY author_name varchar(200) NOT NULL;

Upvotes: 0

danieln

Reputation: 4973

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

Upvotes: 0