MySQL Trigger Creation Executes Without Syntax Error but no trigger created, other executions fail

Question

I'm not receiving an error currently when running the query to create a trigger but after running it I can't execute another query. It is as if I haven't closed some encapsulation:

DELIMITER // CREATE TRIGGER trigger_name BEFORE DELETE ON existingtable for each row begin INSERT INTO new_delete_table (column) values(old.column) end; END DELIMITER;

I am using command line and start with:

mysql>

I execute and receive a new:

mysql>

which is the normal behavior when a query is successful. If I then try to see my triggers I end up in an infinite loop where it is waiting for me to enter some character to close something.

mysql> show triggers;
    ->

I can use ctrl + c to exit the function but that boots me out of MySQL as well. When I log back in my trigger is not present and I can't find any errors.

I've tried it as // DELIMITER; after the first end;. Is that correct? The behavior seemed the same. — user3783243
– user3783243, Commented Sep 1, 2020 at 15:37
The published code is syntactically incorrect end; end is just wrong - did you transcribe correctly? And no the first end should not be there, the insert should be terminated and the begin should be ended (with an end //) and there should be a space between the delimiter keyword and the delimiter, — P.Salmon
– P.Salmon, Commented Sep 1, 2020 at 15:39
@P.Salmon Yes, this is my actual code, with exception of column/table names. — user3783243
– user3783243, Commented Sep 1, 2020 at 15:47

Bill Karwin · Accepted Answer · 2020-09-01 16:23:39Z

1

The DELIMITER command is special. All characters following the command until the end of line are interpreted as a string that you want to use as the new delimiter.

You must not put code on the same line, because all of that code will become part of the new delimiter. This is why you got no error, but no CREATE TRIGGER statement was executed. It only became part of a very long delimiter string.

The reason that DELIMITER must interpret the end-of-line as the end of the command is that it doesn't accept ; as the end of the DELIMITER command. If it did, there would be no way to reset the delimiter back to ;.

You asked in a comment if you need newlines. Aside from the newlines after DELIMITER commands, you do not need newlines. You can do this:

DELIMITER // 
CREATE TRIGGER trigger_name BEFORE DELETE ON existingtable FOR EACH ROW BEGIN INSERT INTO new_delete_table (column) VALUES(old.column) END //
DELIMITER ;

(Remember to use // as the delimiter at the end of the CREATE TRIGGER statement.)

edited Sep 1, 2020 at 16:23

answered Sep 1, 2020 at 16:02

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

user3783243 Over a year ago

Thanks, this worked. The first end also had to removed.

nbk · Accepted Answer · 2020-09-01 16:17:16Z

0

Youhave some errors in your code

every code line must end in a semicolon and you have an END to much

DELIMITER // 
CREATE TRIGGER trigger_name BEFORE DELETE ON existingtable 
for each row 
begin 
    INSERT INTO new_delete_table (`column`) values
    (old.column) ;
end// 
DELIMITER;

edited Sep 1, 2020 at 16:17

answered Sep 1, 2020 at 15:42

nbk

49.6k8 gold badges32 silver badges53 bronze badges

3 Comments

user3783243 Over a year ago

This performs the same. Do I need actual new lines for each statement? I'm going from windows to linux so I'm smushing the syntax to avoid new line errors.

user3783243 Over a year ago

This code did not execute. It had the same behavior as the original.

nbk Over a year ago

check the answer the was a typo, try it please again

Collectives™ on Stack Overflow

MySQL Trigger Creation Executes Without Syntax Error but no trigger created, other executions fail

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related