Why am I unable to create a trigger using my SqlCommand?

Question

The line cmd.ExecuteNonQuery();

cmd.CommandText

CREATE TRIGGER  subscription_trig_0  ON  subscription   AFTER INSERT  AS  UPDATE user_data SET msg_count=msg_count+1 FROM user_data
JOIN INSERTED ON user_data.id = INSERTED.recipient; 

The exception:

Incorrect syntax near the keyword 'TRIGGER'.

Then using VS 2010, connected to the very same file (a mdf file) I run the query above and I get a success message. WTF!

Answer 1

Options

• The 1st line of the actual SQL sent is not CREATE TRIGGER
• CommandType is wrong (eg it's trying to add EXEC or some "prepare" commands)

Use SQL profiler to see exactly what you're sending on to the DB engine (you actually have Express edition that is hosting the MDF)

Answer 2

Do you have CommandType set wrong?

Answer 3

I am not sure why this is failing but if I were you I would use SMO for ddl queries. In this case you need Trigger.Create Method.