printing message in trigger Mysql 5.0

Question

WELL I NEED TO CREATE A TRIGGER ON TABLE RESERVATION IT DOES NOT ALLOW DATE LESS THAN CURRENT DATE.

MY TRIGGER IS :

 mysql> create trigger reserve_trigger before insert on reservation
  -> for each row
-> begin
-> if(new.Journey_Day<curdate())
-> then
-> set NEW="Jouney Date Should not be less than current date";
-> end if;
-> end //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into reservation values ("ppno00005","FSA03","2012-02-09","ECONOMY","MC001","SC003")//
ERROR 1231 (42000): Variable 'new' can't be set to the value of 'Jouney Date Should not be less than current date'

I TRIED TO PRINT MESSAGE USING

    signal sqlstate '45000' set message_text ="invalid entry";

BUT I REALIZED IT DOESN'T WORK FOR MY VERSION OF SQL. IS THERE A BETTER WAY OF DOING IT.

score 0 · Accepted Answer · answered Dec 06 '13 at 08:14

0

Yes, there is. You can call nonexistent stored procedure. For example -

...
BEGIN
  IF NEW.Journey_Day < CURDATE() THEN
    CALL proc_error_date();
  END IF;
END
...

answered Dec 06 '13 at 08:14

Devart

119,203
23
166
186

Find something like - raise error in MySQL. Have a look at this link - http://stackoverflow.com/questions/465727/how-to-raise-an-error-within-a-mysql-function. – Devart Dec 06 '13 at 10:55

printing message in trigger Mysql 5.0

1 Answers1