How to create a database trigger with ADO.NET

Question

I am trying to create a trigger with SqlCommand and I am getting the error:

Incorrect syntax near the keyword 'trigger'

When I copy the same query in SQL Server it is executing successfully.
Here is how the SQL command looks like.

command.CommandText = "CREATE TRIGGER tr_Korisnik" + korisnik.KorisnikID + "_FakturaStavka_ForInsert " +
                        "on Korisnik"+korisnik.KorisnikID+"_FakturaStavka " +
                        "FOR INSERT " +
                        "AS " +                            
                        "BEGIN " +
                        "DECLARE @ID int " +
                        "DECLARE @FakturaID int " +
                        "DECLARE @StavkaBr int " +
                        "SET @ID = (SELECT DokumentID from inserted) " +
                        "SET @FakturaID = (SELECT FakturaID from inserted) " +
                        "UPDATE Korisnik"+korisnik.KorisnikID+"_Fakturi SET BrStavki = BrStavki+1 WHERE DokumentID = @FakturaID " +
                        "SET @StavkaBr = (SELECT Korisnik"+korisnik.KorisnikID+"_Fakturi.BrStavki FROM Korisnik"+korisnik.KorisnikID+"_Fakturi WHERE DokumentID = @FakturaID) " +
                        "UPDATE Korisnik"+korisnik.KorisnikID+"_FakturaStavka SET StavkaBroj =  @StavkaBr WHERE DokumentID = @ID END";


command.ExecuteNonQuery();

Also, above that I have SQLCommands for CREATE TABLE and they work properly.

I tried USE [databasename] before CREATE TRIGGER, still nothing. I removed the concatenations +"korisnik.KorisnikID" and made clean names, still can't execute it.

Answer 1

The documentation for ExecuteNonQuery states that

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Which sort of confirms my suspicions: you can't create a trigger this way.

If you want to create a trigger in code, use a CLR Trigger.

Answer 2

Like already mentioned, a trigger should be created at design time. Nevertheless, as follows it is possible with ExecuteNonQuery.

Use the EXEC statement and the stored procedure sp_executesql:

cmd.CommandText = "EXEC sp_executeSQL N'CREATE TRIGGER myTrigger ON myTable...'";
cmd.ExecuteNonQuery();

Answer 3

You can create a stored procedure in your database to create a trigger then pass the correct parameters. Call the stored procedure with ado.net and pass params.