Two different update triggers for the same table

Question

How can I prevent locking issues between two triggers that fires at the same event on the same table?

The DB I'm working on has already one update trigger that is encrypted and therefore I cannot modify it. I made another update trigger to accomplish some new tasks, it's working correctly when I test it directly on the database, but fails when I make an update to a product on the front-end application. Apparently, when I have my trigger active both triggers fails. The message I get is something like "Document is already open, I'll increment it's value".

Is this a locking issue?

There's a related question where someone says we can have more than one trigger (for same event) on a table.

Here's my triggers code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tr_st_rep_update]
ON [dbo].[st]
AFTER UPDATE
AS
  BEGIN
      SET NOCOUNT ON;

      IF ( update(ref)
            OR update(design)
            OR update(u_update)
            OR update(u_ativo)
            OR update(stock)
            OR update(epv1)
            OR update(epv2)
            OR update(epv3)
            OR update(peso)
            OR update(u_catnv1)
            OR update(u_catnv2)
            OR update(u_catnv3)
            OR update(u_dpromoi)
            OR update(u_dpromof)
            OR update(u_destaque) )
        BEGIN
            IF (SELECT count(*)
                FROM   Inserted
                       INNER JOIN Deleted
                         ON Inserted.ststamp = Deleted.ststamp
                WHERE  inserted.u_ativo = 1
                        OR ( Deleted.u_ativo = 1
                             AND Inserted.u_ativo = 0 )) > 0
              BEGIN
                  INSERT INTO RepData
                              (id,
                               REF,
                               familia,
                               stock,
                               epv1,
                               epv2,
                               epv3,
                               peso,
                               u_accao,
                               imagem,
                               process)
                  SELECT Inserted.ststamp AS id,
                         Inserted.REF     AS REF,
                         Inserted.familia AS familia,
                         Inserted.stock   AS stock,
                         Inserted.epv1    AS epv1,
                         Inserted.epv2    AS epv2,
                         Inserted.epv3    AS epv3,
                         Inserted.peso    AS peso,
                         CASE
                           WHEN Deleted.u_ativo = 1
                                AND Inserted.u_ativo = 0 THEN 'd'
                           ELSE 'u'
                         END              AS u_accao,
                         Inserted.imagem  AS imagem,
                         0                AS process
                  FROM   Inserted
                         INNER JOIN Deleted
                           ON Deleted.ststamp = Inserted.ststamp
                  WHERE  inserted.u_ativo = 1
                          OR ( Deleted.u_ativo = 1
                               AND Inserted.u_ativo = 0 )
              END
        END
  END 

Any help would be appreciated.

Update: Database is MSSQL 2008

Solution 1

Problem solved.

I really don't know the source of the problem although I think it's something related to table locking, in this case the on the Inserted table.

I just changed the inner select statement so that I grab the values directly from the st table instead of the Inserted.

Thanks everyone.

Solution 2

Using triggers to do post-deployment in-site development and customization is an alluring, but bad idea to begin with and will no doubt continually generate problems like this for you.

However, given this, then First: tables can have multiple triggers, that's not the problem.

Secondly, the error message "Document is already open, I'll increment it's value" is either from your client application or from the other (encrypted) trigger, it's NOT a SQL Server error message. Given that, possibly you could try either setting the encrypted trigger to execute first, or set your trigger to execute last. This probably will not fix the problem, but it may move the error from the encrypted trigger, into your trigger where you have a better chance of reporting and/or addressing it in a manageable manner.

Though offhand, the only problem that can see that might be likely from your trigger, is if the other trigger is also writing to the RepData table and your double writing is causing duplicate key violations.

---

Trigger order can be controlled through the sp_settriggerorder system procedure, which is documented here.

Comments

[João Paladini]

Ah, OK. That means that the other trigger was modifying the [st] table rows, which would be reflected in [st] but not in [inserted]. My guess would be that this then caused some key violation elsewhere (possibly in [RepData]).

[Fabio]

Nice answer, thank you! So, how can i manage the order of execution of the triggers?