Use a trigger to stop insert to a table not working MySQL

Question

hi i am using MySQL TRIGGERS , i am using MySQL 5.5.8 , i have a table . since MySQL not allow CHECK Constraint , i used a TRIGGER to check an input value is wrong , if the input value is wrong i tried to gave a message and stop insert the records to the table . but it ignores my TRIGGER , record insert to the table if it contains invalid data .

table

CREATE TABLE pupil_data
(
PupilID int(10) NOT NULL  AUTO_INCREMENT,
PupilForeName varchar(255) NOT NULL,
PupilLastName varchar(255) NOT NULL,
Gender CHAR(1),
DateOfBirth  DATE ,
PhoneNumber int(10) ,
FamilyID int(10) NOT NULL,
PRIMARY KEY (PupilID),
FOREIGN KEY (FamilyID) REFERENCES family_data(FamilyID)  
ON DELETE CASCADE
ON UPDATE CASCADE
,
UNIQUE (PhoneNumber)
) ENGINE=innodb;

trigger

DELIMITER $$
CREATE TRIGGER `check_pupil_before_insert` BEFORE INSERT ON `pupil_data`
FOR EACH ROW  BEGIN
  IF  CHAR_LENGTH( NEW.DateOfBirth ) < 4 THEN
       SIGNAL SQLSTATE '12345'
        SET MESSAGE_TEXT := 'check constraint on pupil_data.DateOfBirth  failed'
    END IF;
END$$  
DELIMITER ;

i want to check if the value entered for DateOfBirth is less than 4 characters , then don't insert the record , give an error message but .

enter image description here

the value of DateOfBirth is 0 , so i suppose it should give an error , but as you can see it is executing ignoring it.

what is the reason , i am doing this wrong , please help, thanks in advance.

UPDATE

enter image description here

The insert statement does indicate a warning, did you try `show warnings`? — Mike Purcell, Mar 21 '13 at 04:48
While creating `trigger` I think you missed out the `semi colon` on error If check. — Ranjith, Mar 21 '13 at 04:50
@MikePurcell , but the insertion happens , can't i stop the insertion ?? — Kanishka Panamaldeniya, Mar 21 '13 at 04:53
Let change this code. `SET MESSAGE_TEXT = 'check constraint on pupil_data.DateOfBirth failed';` And check it out. — Ranjith, Mar 21 '13 at 04:54
@Ranjith , no as Mike mentioned a warning appear , but recoord added to the table — Kanishka Panamaldeniya, Mar 21 '13 at 05:01
@Ranjith , sse my update , but i think it is not oming from the trigger , but the value of DateOFBirt is truncated issue — Kanishka Panamaldeniya, Mar 21 '13 at 05:14
@KanishkaPanamaldeniya Mostly this issue occurs when the `datatype` mismatch on the column field in db. What is the `datatype` of `DateOfBirth` column. — Ranjith, Mar 21 '13 at 05:24

alwaysLearn · Accepted Answer · 2013-03-21T06:48:01.820

3

Your Trigger defination is wrong . You are using date data type and for that you are checking char length . which is probably wrong because mysql by default enters date as '0000-00-00' Plus your syntax is wrong ...

So your Trigger defination should be like

 CREATE TRIGGER `check_pupil_before_insert` BEFORE INSERT ON `pupil_data`
FOR EACH ROW  BEGIN
 IF   NEW.DateOfBirth = '0000-00-00' THEN
   SIGNAL SQLSTATE '12345'
    SET MESSAGE_TEXT = 'check constraint on pupil_data.DateOfBirth  failed';
 END IF;
END$$

Try this ..

edited Mar 21 '13 at 06:48

answered Mar 21 '13 at 06:01

alwaysLearn

6,882
7
39
67

:( , it is adding successully even if i gave 0000-00-00 to DateOfBirth – Kanishka Panamaldeniya Mar 21 '13 at 06:07
have you checked the trigger defination... in database ? I am also using this constraint in my database and it works like charm – alwaysLearn Mar 21 '13 at 06:09
how can i check that ??? – Kanishka Panamaldeniya Mar 21 '13 at 06:12
1

Go to information_schema database then run select ACTION_STATEMENT* from TRIGGERS where TRIGGER_NAME like '%check_pupil_before_insert%'.. – alwaysLearn Mar 21 '13 at 06:17
i checked inside localhost->information_schema->TRIGGERS , but that table has no records :-o – Kanishka Panamaldeniya Mar 21 '13 at 06:26
It means that ur trigger is not created yet .... First confirm that trigger is created.. This trigger defination will surely work .. Well how are you running this sql .. ? if you are running this from GUI of phpadmin then you have to change it a bit .. – alwaysLearn Mar 21 '13 at 06:28
i am running from PhpMyadmin , please help me , how shuld i change this , but when i run the trigger in PHPMyadmin it adds the trigger successfully – Kanishka Panamaldeniya Mar 21 '13 at 06:35
1

It will not show any error but your trigger is not created until you have successful trigger creation message .. ok now I am changing my answer .. rum that query in phpmyadmin .. But before that change delimeter in delimeter box (below sql query box) to $$ . – alwaysLearn Mar 21 '13 at 06:47
@KanishkaPanamaldeniya Are you able to create Trigger .. ? – alwaysLearn Mar 21 '13 at 07:09
mm no still the same problem – Kanishka Panamaldeniya Mar 21 '13 at 07:10
+1 for fixing, personally I don't like implementing triggers, they add a level of complexity which should be handled at the app layer. – Mike Purcell Mar 21 '13 at 16:26

Use a trigger to stop insert to a table not working MySQL

1 Answers1