How to return error Message in Output from Trigger?

Question

I am trying to create trigger that ensures that the same shop_id should not repeat more than 3 times, and if user tries to add more than 3 records for the same shop_id, it will throw an error message. I have written the following trigger but its giving me error message.

  IF
    (sum(distinct(shop_id)) >3 )
    SET MESSAGE_TEXT := 'You cannot insert more than 3 Records';
    END IF;

Its giving me following error message.

    MySQL said: #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 'SET MESSAGE_TEXT 
:= 'check constraint on

Kindly check this and guide me what i am doing wrong here.

enter image description here

juergen d · Accepted Answer · 2014-10-10 08:15:08Z

2

SIGNAL SQLSTATE '45000'
       SET MESSAGE_TEXT = 'You cannot insert more than 3 Records';

SIGNAL Syntax

answered Oct 10, 2014 at 8:15

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stack Over a year ago

And how should I use of that message in php?

Collectives™ on Stack Overflow

How to return error Message in Output from Trigger?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related