how to show a message if condition is true in mysql 5.0 trigger?

Question

I have a trigger

 DELIMITER $$
  DROP TRIGGER IF EXISTS before_insert_on_emp $$
  CREATE TRIGGER before_insert_on_emp
  BEFORE insert ON empefforts
  FOR EACH ROW BEGIN
  DECLARE MSG VARCHAR(100);
  IF (NEW.TIMING) > 60
  THEN
    SET MSG='Error: TIMING must be <=60.';
  END IF;
 END$$
DELIMITER ;

I want to show the MSG VARIABLE value on console when the condition is true. How can I show this value. I am using mysql 5.0.18 version...

There is no normal way to do it in MySql ver < 5.5. You can try one of the hackish ways (read e.g. http://stackoverflow.com/q/229765/1920232, http://stackoverflow.com/q/24/1920232) — peterm, Jun 12 '13 at 07:51
@Stephan Yes I tried but it gives me error:Not allowed to return Resultset from a trigger. — Mahesh Patidar, Jun 12 '13 at 08:47
@MaheshPatidar sorry my bad just now i saw that it was a trigger... in this case the only option is to insert the message in a log table — Stephan, Jun 12 '13 at 08:59
@MaheshPatidar from a trigger i do not think so ... i know you can do this in a stored procedure — Stephan, Jun 12 '13 at 09:19

Stephan · Answer 1 · 2013-06-12T09:35:53.640

0

After doing some research i found that there is a way ... using SIGNAL like:

SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Error: TIMING must be <=60.';

UPDATE: example of procedure that prints a message:

DELIMITER //
CREATE PROCEDURE show_message()
BEGIN
    SELECT 'Error: TIMING must be <=60.'
END//

edited Jun 12 '13 at 09:35

answered Jun 12 '13 at 09:22

Stephan

8,000
3
36
42

SIGNAL not support in mysql 5.0 – Mahesh Patidar Jun 12 '13 at 09:23
I am not so much good in procedure so can you write an example of stored procedure to show the example since I tried using procedure also but not worked at all – Mahesh Patidar Jun 12 '13 at 09:27

how to show a message if condition is true in mysql 5.0 trigger?

1 Answers1