c# - 如果尚未创建，如何从 C# 以编程方式创建 MySQL 触发器？

Question

我想仅在未创建时以编程方式创建触发器。

我无法从 C# 以编程方式创建触发器。如果我从 MySQL Workbench 6.3 执行相同的触发器而不是它成功创建的触发器。

我在从 C# 执行时收到错误，如下所示。

Error at line

cmd.ExecuteNonQuery(). "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 Trigger_Lifetouch AFTER INSERT ON mat.lifetou' at line 1"

我的代码如下

public void createTrigger()
    {
            string _query = " DELIMITER // " +
                            " CREATE "+
                            " TRIGGER `Trigger_Lifetouch`  AFTER INSERT  "+
                            " ON mat.lifetouch "+
                            " FOR EACH ROW BEGIN "+
                            " INSERT INTO mat.lifetouchrespirationrate (ByDevSessionId) VALUES(2);       "+
                            " END;" ;

            using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(connectionstring))
            {
                using (var cmd = con.CreateCommand())
                {
                    if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
                    {
                        con.Open();
                    }

                    int retn = 0;

                    using (var transaction = con.BeginTransaction(IsolationLevel.ReadCommitted))
                    {
                        retn = 0;
                        cmd.Transaction = transaction;
                        cmd.CommandText = _query;

                        try
                        {
                            retn = Convert.ToInt32(cmd.ExecuteNonQuery().ToString());

                            if (retn > 0)
                            {
                                logger.Debug(" Trigger Created ");
                            }
                            else
                            {
                                logger.Debug("  Trigger Not Created  ");
                            }
                        }
                        catch (Exception ex)
                        {
                            logger.Debug(ex );
                        }

                    }
                }
            }

Answer 1

触发器的创建不能通过MySqlCommand来完成。你应该使用 (new MySqlScript([ConnectionString], [TriggerScript])).Execute();

这将创建触发器。