Error executing SQL "Create Trigger ..." within a C #

Question

Error executing SQL "Create Trigger ..." within a C #

Navigation

#1 by (0 votes)

0

Goal: Run a create trigger script using a string inside C #.

Problem 1: Table not found (because the script does not indicate in which Table to create the trigger).

Attempt2: Start the string with "Create Trigger on [dbo]. [Table]".

Problem 2: For Trigger creation SQL does not allow pre-naming the Bank.

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

Problem 3: The "Create Trigger" command should always be the first one in the script. In addition, it can not interpret the GO command because 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? Thankful.

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

sql sql-server c# trigger create

asked by anonymous 14.06.2017 / 14:23

1 answer

Creating the "persistence.xml" JPA / Hibernate file Allow a certain logged-in ID only

score 0 · Answer 1

I did it and it worked, see if it suits you. One tip is to try to simplify your command (in this case the trigger) to make sure 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 console application:

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: