0

I have a table called tbemissoes. When there is an insert, I need to execute this procedure :

create procedure exec pr_replicaRemessa --49 ,  2016
@RemessaNumero int,
@RemessaAno int

AS
Begin


--Faço Insert na tabela Reps do banco do Portal de Remessa Eletronica
insert into PortalRemessasEletronicas.dbo.Reps(NumeroRep,AnoRep)
(
select r.numero, r.ano from tbReps r
inner join tbRepEmissao re on re.cod_rep = r.cod_rep
inner join tbEmissoes e on e.cod_emissao = re.cod_emissao
where  r.cod_rep = re.cod_rep and e.numero = @RemessaNumero and e.ano = @RemessaAno
)


--Faço Insert na tabela Origem do POrtal de Remessa Eletronica
insert into PortalRemessasEletronicas.dbo.Origem(TipoOrigemId, numero, ano, NomeOrgao, RepId)
(
select  o.cod_tipoorigem, o.numero, o.ano, o.orgao, pr.RepId from tbOrigens o
inner join tbReps r on o.cod_rep  = r.cod_rep
inner join tbRepEmissao re on re.cod_rep = r.cod_rep
inner join tbEmissoes e on e.cod_emissao = re.cod_emissao
inner join PortalRemessasEletronicas.dbo.Reps pr on pr.NumeroRep = r.numero and pr.AnoRep = r.ano
where r.cod_rep = o.cod_rep  and e.numero=@RemessaNumero and e.ano=@RemessaAno and o.cod_tipoorigem in (2,3,7)
)

--
insert into PortalRemessasEletronicas.dbo.Remessas (NumeroRemessa,AnoRemessa,DataEmissao,Equipe,Nucleo,OrigemId, RepId, OrgaoDestino)
    (
         select ge.numero as 'NumeroRemessa', ge.ano as 'AnoRemessa', ge.datahora_emissao as 'DataEmissao', ue.equipe as 'Equipe', un.nucleo as 'Nucleo', po.OrigemId as 'OrigemId', pr.RepId as 'RepId',ge.destinatario as 'OrgaoDestino'  from PortalRemessasEletronicas.dbo.Reps pr
         inner join PortalRemessasEletronicas.dbo.Origem po on po.RepId = pr.RepId
         inner join tbReps gr on gr.numero = pr.NumeroRep and gr.ano = pr.AnoRep
         inner join tbEmissoes ge on ge.cod_emissao = gr.cod_emissao
         inner join tbUnidades u on u.cod_unidade = ge.cod_unidade
         inner join lstUnidadesNucleos un on u.cod_nucleo = un.cod_nucleo
         left join lstUnidadesEquipes ue on ue.cod_equipe = u.cod_equipe
         where pr.NumeroRep = gr.numero and pr.AnoRep = gr.ano and ge.numero = @RemessaNumero and ge.ano = @RemessaAno
    )
end

This is the trigger:

alter trigger tr_replicaRemessa 
    on tbemissoes
    for insert 
        as
            begin
                declare @RemessaNumero int,
                        @RemessaAno int

                        select @RemessaNumero = numero, @remessaano = ano from inserted

                        exec pr_replicaRemessa @RemessaNumero,@remessaano

                        end

Using Profiler, I can see that the trigger is executed:

enter image description here

But why is this data not being inserted? Even with after insert it's not working.

Manually, when I create a scenario, inserting values in the tbemissoes, I have this:

enter image description here

0 rows affected.

When I run the procedure manually, with the desired variables, it inserts ok in those 3 tables inside the procedure.

Again with Profiler, it runs the "exec" before the "exec" command in the trigger. how is this possible?

enter image description here

  • 3
    (1) trace rows are not necessarily in Profiler's brittle UI in the precise order they occurred - did you check start time etc.? (2) I am not sure how we can determine why your inserts aren't happening, but I can suggest that you simplify the stored procedure - maybe add a very simple insert first that doesn't rely on joins, then you can be sure the trigger is running through. (3) This is a real problem: select @RemessaNumero = numero, @remessaano = ano from inserted - this assumes that all inserts, ever, will only ever affect a single row. – Aaron Bertrand May 13 '16 at 20:25
  • @AaronBertrand for sure I will fix these issues. I just found out that a major procedure was running with begin transaction and the table being inserted was the first one, and there was a lot more tables being 'updated". I set a validation values in another table, and in the end of the transaction, I could start the trigger. And I'm using now select @trRemessaNumero = tbEmissoes.numero, @trremessaano = tbemissoes.ano from tbEmissoes where cod_emissao = (select max(cod_emissao) from tbEmissoes). – Racer SQL May 16 '16 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.