这似乎是我遗漏了一个简单的细节,我只是不明白是什么。
有了这个
string deleteTrigger =
"IF OBJECT_ID('@p0') IS NOT NULL " +
"DROP TRIGGER [@p1] ";
string createTrigger =
"CREATE TRIGGER [@p0] " +
"ON [dbo].[@p1] " +
"AFTER DELETE AS " +
"BEGIN " +
"SET NoCount ON " +
"DELETE FROM [dbo].[MyTable] WHERE ID IN (SELECT ID FROM DELETED) " +
"END ";
object[] parameterList = new object[] {"Tr_SomeTable_AD", "Tr_SomeTable_AD" };
context.Database.ExecuteSqlCommand(deleteTrigger,parameterList); // Works
parameterList = new object[] { "Tr_SomeTable_AD", "SomeTable" };
context.Database.ExecuteSqlCommand(createTrigger, parameterList); // Exception thrown here!
in my InitializeDatabase(DbContext)
抛出异常:
SqlException:“TRIGGER”一词附近的语法不正确
在第二个ExecuteSqlCommand
(第一个工作正常)。我也尝试过
context.Database.ExecuteSqlCommand(createTrigger, "Tr_SomeTable_AD", "SomeTable");
而不是使用object[]
,但结果是一样的。
I saw 这个问题 https://stackoverflow.com/questions/21973627/executesqlcommand-on-seed-to-create-trigger-with-entityframework-throws-sqlexcep,但答案并没有解决我的问题。我知道问题一定与我使用参数有关,因为如果我只是将值放入 createTrigger 字符串中,SQL 命令就会起作用。但是,我希望对其进行参数化,以便更轻松地在不同的表上使用相同的触发器创建代码。
解决方案:
正如 @marc_s 所说(参见下面的答案),我无法在 T-SQL 中参数化表或列(或触发器)名称 - 我必须在 C# 中创建完整的最终 T-SQL 语句(使用 string.Format() )。因此:
string deleteTrigger = "..."; // same as before
string createTrigger =
"CREATE TRIGGER [dbo].[{0}] " +
"ON [dbo].[{1}] " + ...
object[] parameterList = new object[] {"Tr_SomeTable_AD", "Tr_SomeTable_AD" };
context.Database.ExecuteSqlCommand(string.Format(deleteTrigger,parameterList));
parameterList = new object[] { "Tr_SomeTable_AD", "SomeTable" }; // Use string.Format() here!
context.Database.ExecuteSqlCommand(string.Format(createTrigger, parameterList));
就可以了。