3

Ok so i'm working on triggers, and it tells me it(MySQL workbench 5.2) doesn't recognize this variable.
*Error Code: 1193. Unknown system variable error_msg_3*

I think it would be correct using it in a trigger, please help me

CREATE TRIGGER controlla_MaxCARDINALITA_INSERT
BEFORE INSERT ON SENTIERO__HA__TAPPA
FOR EACH ROW 
BEGIN

DECLARE max_cardinalita INTEGER;
DECLARE error_msg_3 CONDITION FOR SQLSTATE '99003';

SELECT COUNT(*) into max_cardinalita
FROM SENTIERO__HA__TAPPA
WHERE IDsentiero=NEW.IDsentiero;

IF max_cardinalita>=10 THEN
        SIGNAL error_msg_3;
        SET error_msg_3='INSERT: Il sentiero ha già il massimo numero di tappe consentito';

END IF;

END$$

EDIT ::

I tried this, and it seems working

DECLARE msg VARCHAR(255);
set msg = concat('MyTriggerError: Trying to insert a negative value in trigger_test: ');
signal sqlstate '45000' set message_text = msg;
FrancescoN
  • 2,146
  • 12
  • 34
  • 45

1 Answers1

3

According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.

Message: Unknown system variable %s

And, as you can see on the same page, the SQLSTATE 99003 is not defined.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • please see my edit, why does that code work? there isn't 45000 in the table, i think – FrancescoN Feb 04 '13 at 00:58
  • @Jimmy5nomana Please again, refer to the docs on usage of [`SIGNAL`](http://dev.mysql.com/doc/refman/5.5/en/signal.html) clause. It clearly mentions: **SIGNAL is the way to “return” an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. To signal a generic SQLSTATE value, use '45000', which means _unhandled user-defined exception_.** – hjpotter92 Feb 04 '13 at 01:02
  • Thanks, last question for you: mysql doesn't let me using multiple triggers for the same event and action time for one table... so i heared about functions and procedures; are them the solution? I'd like to solve it in the easiest way, i'm in hurry. Thanks – FrancescoN Feb 04 '13 at 01:08
  • @Jimmy5nomana For that, you'd get better advices over at [DBA stackexchange](http://dba.stackexchange.com/) – hjpotter92 Feb 04 '13 at 01:10
  • That's not theory!!! if you have 3 triggers as these: AFTER UPDATE ON TABLE 1... how could they run on that table? – FrancescoN Feb 04 '13 at 02:02