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.

Triggers are meant for automatic execution, when a DML like Update, Insert, Delete is run. You cannot create or execute a trigger using ADO.Net APIs — Mrinal Kamboj, Jun 28 '15 at 02:01
Take a look at this [link](http://stackoverflow.com/questions/2868704/why-am-i-unable-to-create-a-trigger-using-my-sqlcommand) — Raj Karri, Jun 28 '15 at 02:35
1) This trigger is wrong because it assumes that INSERT statement/action will insert only one row (`SET @ID = (SELECT DokumentID from inserted)`). 2) Most of the time, creating a trigger is a one time job: they are not created at runtime. Instead they are created using SQL Server Manegement Studio. 3) Please explain the goal of this trigger. — Bogdan Sahlean, Jun 28 '15 at 03:26
What is your SQL Server version ? SELECT @@version ! I hope you don't have compact edition ! — PollusB, Jun 28 '15 at 04:58
This seems like a design flaw. Triggers, like tables, should not be created or altered in runtime, but in design time. — Zohar Peled, Jun 28 '15 at 06:36

score 1 · Answer 1 · answered Jun 28 '15 at 01:49

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.

answered Jun 28 '15 at 01:49

Robert Harvey

178,213
47
333
501

CLR Trigger is still a class doing the same job a Create Trigger in Sql statements. ADO.Net APIs cannot execute the statements like Create Trigger – Mrinal Kamboj Jun 28 '15 at 02:03
@MrinalKamboj: Yes, that's what I said. – Robert Harvey Jun 28 '15 at 02:25

score 1 · Answer 2 · answered Jun 28 '15 at 03:08

1

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.

answered Jun 28 '15 at 03:08

MIKE

1,039
7
24

MatSnow · Answer 3 · 2018-12-13T07:15:09.173

0

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();

edited Dec 13 '18 at 07:15

answered Dec 12 '18 at 11:23

MatSnow

7,357
3
19
31

How to create a database trigger with ADO.NET

3 Answers3