sql server - Create sql trigger dynamically and rollback if error -

I am creating a stored procedure that will trigger 3 triggers (insert, update, delete) a table name .

Here is an example to illustrate this issue I am experiencing:

  making process [dbo] Start as [Sp_test] Tran - Trigger 1 DECLARE @sql NVARCHAR (Max) = 'Trigger Number 1' as 'Exchange SP_executesql @sql' on Trigger Number 1, but this one will fail because in Table_1 There is an Entext field. SET @sql = 'Create Troubles 1 on Trigger Test 1 - Since Instal * Select Exchange from Search End EXEC SP_XECTSQL = SQL COMMIT TRAN END  

So I thought a transaction The first trigger will not be made by wrapping the call, since the second will fail but the first trigger is made anyway .... How can I prevent it from happening? I want to make the whole thing nuclear.

Try it, try it from BEGIN

  the process of creating [Dbo]. Start as [Sp_test] TRAN DECLARE @sql NVARCHAR (MAX) = 'Make Tasters on Triser Start Tables XML after INSTATE' EXEC SP_EXECTUTSQL @SQLL SET @sql = 'Create Trigger INSERT on Table 1 at Test1 Start as * Choose from the start * EXEC SP_XECTSQL = sql COMMIT TRAN Finally the catch rehearser ('Ermermesses', 18, 1) rollback tray and catch and