Error running SQL "Create Trigger..." inside a C#application

Question

Goal: Run a create Trigger script using a string within C#.

Problem1: Table not found (because the script does not indicate in which Table to create Trigger).

Tentative2: Start string with "Create Trigger on [Bank]. [dbo]. [Table]".

Problema2: For Trigger creation SQL does not allow to pre-name the Database.

Tentative3: Start string with " Use [Bank] GO Create Trigger..."

Problema3: The "Create Trigger" command must always be the first one in the script. Also it cannot interpret the GO command, as it is not native to SQL.

I have no other option, I tried to do via stored Procedure, the same problems occur. Please, has anyone ever faced this problem before? Grateful.

    strSql = "      CREATE TRIGGER [dbo].[TGR_ALERTA]           ";
                    strSql += " ON [dbo].[Alertas_Integracao]           ";
                    strSql += "     after INSERT            ";
                    strSql += "     AS          ";
                    strSql += " If (SELECT Defeito FROM INSERTED) = 1   ";
                    strSql += "         begin       ";
                    strSql += "     BEGIN           ";
                    strSql += "         DECLARE     ";
                    strSql += "         @EQUIPAMENTO_ID int,        ";
                    strSql += "         @SETOR_ID   int,    ";
                    strSql += "         @PROBLEMA int,      ";
                    strSql += "         @INTEQUIPAMENTO int,        ";
                    strSql += "         @STRALERTA varchar(max),        ";
                    strSql += "         @IDEVENTO int       ";
                    strSql += " select @INTEQUIPAMENTO = Equipamento from Inserted      ";
                    strSql += "         select @STRALERTA = Alerta from Inserted        ";
                    strSql += "         select @EQUIPAMENTO_ID = (SELECT ieq_equ_equipamentos_id        ";
                    strSql += "         from EQU_IEQ_INTEGRACAO_EQUIPAMENTOS        ";
                    strSql += "         where ieq_equipamento_integrado =  @INTEQUIPAMENTO)     ";
                    strSql += "         select @SETOR_ID = (Select equ_sto_setores_id       ";
                    strSql += "         from EQU_EQU_EQUIPAMENTOS       ";
                    strSql += "         where equ_equipamentos_id = @EQUIPAMENTO_ID)        ";
                    strSql += "         select @PROBLEMA = (Select prb_problemas_id     ";
                    strSql += "         from EVE_PRB_PROBLEMAS      ";
                    strSql += "         where prb_descricao = @STRALERTA        ";
                    strSql += "         and prb_equ_equipamentos_id = @EQUIPAMENTO_ID)      ";
                    strSql += "         insert into EVE_EVE_EVENTOS     ";
                    strSql += "         (eve_usu_usuarios_id,       ";
                    strSql += "         eve_sto_setores_id,     ";
                    strSql += "         eve_sta_status_id,      ";
                    strSql += "         eve_data_inclusao,      ";
                    strSql += "         eve_problema,       ";
                    strSql += "         eve_corretiva,      ";
                    strSql += "         eve_equ_equipamentos_id,        ";
                    strSql += "         eve_inativo,        ";
                    strSql += "         eve_prb_problemas_id)       ";
                    strSql += "         values      ";
                    strSql += "         (2,     ";
                    strSql += "         @SETOR_ID,      ";
                    strSql += "         6,      ";
                    strSql += "         GETDATE(),      ";
                    strSql += "         @STRALERTA,     ";
                    strSql += "         1,      ";
                    strSql += "         @EQUIPAMENTO_ID,        ";
                    strSql += "         0,      ";
                    strSql += "         @PROBLEMA       ";
                    strSql += "         )       ";
                    strSql += "         SELECT @IDEVENTO = @@IDENTITY       ";
                    strSql += "         insert into EVE_SEV_STATUS_EVENTOS      ";
                    strSql += "         values (@IDEVENTO, 6, getdate())        ";
                    strSql += "     END         ";
                    strSql += "     end         ";  

clsCon.ExecutarSql(strSql);

Answer 1

I did it and it worked. A tip is to try to simplify your command (in case Trigger) to ensure that the problem is not there.

I created the tables below:

create table [dbo].TB_TESTE(
 id int identity ,
 nome varchar(100),
 idade int
)

create table [dbo].TB_TESTE_HIST(
 id int identity ,
 nome varchar(100),
 [Data] datetime
)

And I created the following application console:

namespace ConsoleApp2
{
    class Program
    {

        private static string MyTrigger =
@"CREATE TRIGGER [dbo].[TRG_Exemplo_UPD] ON [dbo].[TB_TESTE]
    AFTER UPDATE
  AS
  BEGIN
    SET NOCOUNT ON;

    if(UPDATE([IDADE]))
    BEGIN

        Declare @nome VARCHAR(100);
        select @nome = nome from inserted ;
        insert into [dbo].TB_TESTE_HIST ([Nome], [Data]) values (@nome, getdate());

    END;    
  END;";

        static void Main(string[] args)
        {
            var cnx = "Server=localhost; Database=Dev-Test; Trusted_Connection = false; User Id=sa; Password =XXXX; Persist Security Info=true; MultipleActiveResultSets=True";
            var cnn = new System.Data.SqlClient.SqlConnection(cnx);
            try
            {
                cnn.Open();
                var cmd = new System.Data.SqlClient.SqlCommand(MyTrigger, cnn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cnn.Close();
            }
        }
    }
}

The final result was the Trigger created in the database: