Comments - Trigger keeps creating error

8 years, 7 months ago Elena Stepanova

Delimiters show the client program (HeidiSQL in your case) where a query ends, and the client sends the entire chunk of SQL to the server as a single query. The default delimiter is semicolon. So, in your initial example, the client sends everything from CREATE and up and including SET NEW.ClGender ='U', which of course does not make a complete valid query. This is what the server complains about in the error message -- it finds emptiness where it expects something else.

When you change the delimiter and use it at the end of the trigger definition, the client understands that the whole definition should be sent as a whole, and the server is able to interpret it correctly.

It is perfectly fine to use non-default delimiters when you define stored procedures (including triggers); you don't need to invent tricks to avoid it. It is not always possible anyway.

But if you are curious, in your case you could phrase it like this:

CREATE TRIGGER trig_client_gndr 
SET NEW.ClGender = IF(NEW.ClGender NOT IN ('M','F'),'U',NEW.ClGender);

I think it should have worked.

8 years, 7 months ago Kaigh Taylor

Thank you for that follow up. Now that all makes sense. I have been using Oracle for school, but I am now using MariaDB at home.

The two RDBMS have enough differences to be frustrating when migrating.

Again, thanks.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.