'How to rewrite a trigger causing an update deadlock error

I have the following trigger. It is executed after an update statement from a REST API request. My issue is that when it is sent two API calls concurrently, the trigger is executed twice, therefore a deadlock error comes up because the transaction is not finished. Is there any SQL trickery I can try? Can I check in table B if it already has B_NEEDSRECALCULATION set to '+' and bypass the update, or maybe create a trigger on tableb?

CREATE OR ALTER TRIGGER TR_TESTTRIGGER_AU FOR TABLEA
ACTIVE AFTER UPDATE POSITION 0
AS
   
begin

  if (rdb$get_context('USER_TRANSACTION', 'WISASCRIPT') = '1') then exit;
 
  if ((new.A_FiledB IS NULL) and 
      (old.A_FieldA <> new.A_FieldB) )THEN 

  begin
    UPDATE TableB        
    SET B_NEEDSRECALCULATION = '+'
    WHERE (B_TESTID = new.A_TESTIDFK);
  end

end
sqltriggersfirebirdfirebird2.5


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions