TRIGGER CREATED successfully , but no triggers in information_schema TRIGGERS table

Question

hi i am trying to use triggers , my MySQL version is 5.5.8

the thing is when i create the trigger , PHPMyadmin says it is created successfully ,

this is my 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 ;

it says

Your SQL query has been executed successfully
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 ;

but when i

select * from information_schema.triggers

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0843 sec )
SELECT * 
FROM information_schema.triggers
LIMIT 0 , 30

why is this happening , please help me , thanks in advance.

an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 — Kanishka Panamaldeniya, Mar 21 '13 at 07:15
Hmm, SHOW CREATE TRIGGER should show the trigger's information or return empty result-set. ...Try new_developer's solution. — Devart, Mar 21 '13 at 07:20
@Devart thank you for the help. new_developers suggesion worked perfectly . — Kanishka Panamaldeniya, Mar 21 '13 at 07:23

score 2 · Accepted Answer · answered Mar 21 '13 at 07:15

2

Check this.. It will help you ... and make sure query is same as is shown in image change ":=" to "="

answered Mar 21 '13 at 07:15

alwaysLearn

6,882
7
39
67

wow that worked . and the validation also works . brilliant answer , thank you so much , i worked more than 10 hours to solve this :D – Kanishka Panamaldeniya Mar 21 '13 at 07:22

TRIGGER CREATED successfully , but no triggers in information_schema TRIGGERS table

1 Answers1