1

I have following command which creates trigger in database

command.ExecuteNonQuery();

The trigger is created successfully. But how Can I detect if It has been created while executing command ?

So far I managed to execute separated command that check if trigger allready exists in database, but what I need is to check while it is being created.

string insert =  " CREATE TRIGGER TraNag_UpdateInsertOpis ON dbo.TraNag"
+ " FOR INSERT,UPDATE"
+ " AS"

+ " DECLARE @OpisInsert varchar(1024)"
+ " DECLARE @TrnSymbolInsert varchar(40)"

+ " select @TrnSymbolInsert = TrN_Symbol, @OpisInsert = Trn_Opis from inserted "

+ " IF (@TrnSymbolInsert = 'MMW')"
+ " BEGIN"
+ " IF NOT EXISTS("
+ " SELECT Mag_Symbol"
+ " FROM dbo.Magazyny"
+ " WHERE Mag_Symbol like @OpisInsert"
+ " )"
+ " BEGIN"
+ " RAISERROR('Error NOT FOR STACKOVERFLOWWWW !!! : %s', 16, 1, @OpisInsert);"
+ " END END";
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
corso
  • 49
  • 10
  • Well, it hasn't been created until the command is done, so I'm not sure how you can check that until then. What's wrong with executing two commands? Is there a reason to think that it _hasn't_ been created? – D Stanley Feb 10 '16 at 13:39
  • 1
    Can you show the commandtext of `command`? Does it contain a `CREATE TRIGGER` statement? – Tim Schmelter Feb 10 '16 at 13:40
  • All `command.ExecuteNonQuery()` does is execute the SQL command and ignores any result set. If you want to execute multiple commands in one batch then modify the SQL statement in the `command` object. – D Stanley Feb 10 '16 at 13:40
  • command text has been updated. again – corso Feb 10 '16 at 13:45
  • 1
    Look here: http://stackoverflow.com/questions/636452/what-is-the-most-portable-way-to-check-whether-a-trigger-exists-in-sql-server So either 1.) `select case when exists(select 1 from sys.triggers where name = 'TraNag_UpdateInsertOpis')then 1 else 0 end` or 2.) `IF OBJECT_ID ('dbo.TraNag_UpdateInsertOpis', 'TR') IS NOT NULL` – Tim Schmelter Feb 10 '16 at 13:49

1 Answers1

2

Simply check before & after running your ExecuteNonQuery whether the trigger exists. This will detect whether trigger has been created already or by your command or not created at all.

  • If the trigger doesn't exist before and does exist after `ExecuteNonQuery` it indicates that a trigger was created. It's possible that the trigger was created during that interval by another session. In this case there may be no competition, but in general it has to be taken into consideration. – HABO Feb 10 '16 at 21:31