SQL Create Trigger Command Runs Fine Through MySQL Workbench But Errors When Called Through Entity Framework

Question

I have the following code to create SQL setup a trigger (not yet a prepared statement).

commandList.Add($"DROP TRIGGER if exists `{tableName}_after_insert`;");
commandList.Add($"delimiter $$" + Environment.NewLine +
    $"CREATE TRIGGER `{tableName}_after_insert` AFTER INSERT" + Environment.NewLine +
    $"ON `{tableName}`" + Environment.NewLine +
    "FOR EACH ROW BEGIN" + Environment.NewLine +
    "Insert into HashEntry(TableName, TableRowId, TokenValue)" + Environment.NewLine +
    $"select '{tableName}', n.{tableKey}, 'tokenValue' from NEW n;" + Environment.NewLine +
    "END;" + Environment.NewLine +
    "$$" + Environment.NewLine +
    "delimiter ;");

//then some unrelated code until we execute

foreach (var command in commandList)
{
    Database.ExecuteSqlCommand(command);
}

This create the following SQL which works in Workbench and Entity Framework.

DROP TRIGGER if exists `MyTable_after_insert`;

And this which only works in Workbench directly.

delimiter $$
CREATE TRIGGER `MyTable_after_insert` AFTER INSERT
ON `MyTable`
FOR EACH ROW BEGIN
Insert into HashEntry(TableName, TableRowId, TokenValue)
select 'MyTable', n.Id, 'tokenValue' from NEW n;
END;
$$
delimiter ;

Which gives the following error.

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 'delimiter $$
CREATE TRIGGER `MyTable_after_insert` AFTER INSERT

I'm not sure why it would work directly in Workbench but not calling the same command through EF.

Any ideas?

At a guess maybe because you are using $$ possibly inside the string, what if you change it to something else? its like it doesnt like the delimeter — BugFinder
– BugFinder, Commented Jun 14, 2019 at 9:56
I tried with three underscores ___ and it didn't work either. — Geesh_SO
– Geesh_SO, Commented Jun 14, 2019 at 10:02

Bradley Grainger · Accepted Answer · 2019-06-16 20:04:13Z

1

DELIMITER $$ is special syntax only understood by MySQL Workbench. It's not necessary when executing commands against a server using MySqlCommand. Just execute the SQL directly:

commandList.Add($@"CREATE TRIGGER `{tableName}_after_insert` AFTER INSERT
    ON `{tableName}`
    FOR EACH ROW BEGIN
    Insert into HashEntry(TableName, TableRowId, TokenValue)
    select '{tableName}', n.{tableKey}, 'tokenValue' from NEW n;
    END;");

(Note that I'm using verbatim strings to avoid all the concatenation with Environment.NewLine.)

answered Jun 16, 2019 at 20:04

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

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

1 Comment

Geesh_SO Over a year ago

That seems incredibly obvious now. It worked, thank you.

Collectives™ on Stack Overflow

SQL Create Trigger Command Runs Fine Through MySQL Workbench But Errors When Called Through Entity Framework

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