• If you look closer the in the after-trigger trace, you will find that it is only the first invocation that takes a longer time. Furthermore, if you look close at the times, you will find this:

    This statement fires the trigger:

    UPDATE AA SET QtReservada = 0 FROM Inserted I INNER JOIN LinhasDoc LD ON LD.Id=I.IdLinhasDoc INNER JOIN ArtigoArmazem AA ON AA.Artigo=LD.Artigo --BID: 583194

    It starts executing at 2013-09-20 10:44:00.187

    The first command in the contested trigger is SET NOCOUNT ON, and this command starts executing at 2013-09-20 10:44:00.240. (You need to look at the SET NOCOUNT ON that are a few lines above, since you have xxx:Completed events.

    This is typically what you see when a module is compiled. Since the new trigger includes a more complex query than the old trigger it takes longer time to compile, but if the plan stays in cache, this overhead can be neglected.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]