How do I set sql delimiter through R code?

I need to create a trigger in sql server via R code for which I need to set my sql delimiter to //. I tried doing the following:

dbExecute(con, "delimiter //")

dbExecute(con, "delimiter //\n")

dbExecute(con, "delimiter //\t")

I also tried the above scenarios with other DBI functions like

dbGetQuery and dbSendQuery

but I am getting the following error.

could not run statement: 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 //' at line 1

Solution

It turns out that in order to execute an sql trigger through R using the DBI package, one does not need to set and unset the delimiter. We can directly execute the trigger command.

This is unlike what needs to be done while setting a triggers through SQL command line where, since the trigger syntax itself includes a semicolon ;, in order to avoid conflict with the default SQL delimiter which is also ; we temporarily set the delimiter to a lesser used special character such as // with a command such as

delimiter //

and then revert back to the default delimiter with

delimiter ;

which need not be done when trigger is executed through DBI package of R.