Comments - Trigger keeps creating error
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.
That worked perfectly. Can you be so kind as to explain why it worked, and how I would write that statement without semicolons? I tried to write it without any semicolons and it gave me an error at the END IF line. Of course I removed the Delimiter statements on that attempt also.
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
CREATEand 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:
I think it should have worked.
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.