Trigger updates only to 1 record

Asked

Viewed 605 times

1

Considering the following trigger:

CREATE TRIGGER tgr_vendas_d
ON vendas
FOR DELETE
AS
BEGIN
  DECLARE @valor DECIMAL(10,2),
          @data  DATETIME;

  SELECT @data = d.data,
         @valor = d.valor
    FROM deleted d

  UPDATE c
     SET saldo_final = saldo_final - @valor
    FROM caixa c
   WHERE c.data = @data
END
GO

If more than one record is deleted, only the last value is updated. Considering this, I have two questions:

1) Why this happens?

2) How to fix the problem efficiently?

database sql-server trigger
  • 1

    http://www.devmedia.com.br/cursores-no-sqlserver/5590 you will need a Cursor

  • @Motta exactly. It has this shape and one without cursor too. You can add the answer?

  • @Sorack: When a sale line is deleted, shouldn’t the cash balance be updated for other later dates as well? That is, the one-day cash balance is used as the starting balance the next day or the balance is individually controlled for each day?

  • @Josédiz yes... This problem is not real, it’s just an example I put to facilitate understanding

2 answers

2


This is because when you delete on multiple lines in the same statement, it activates Trigger only once and in its temporary table deleted, will get the list of all deleted.

There are various solutions to your problem, you could take a courses for example and run your update several times, do not recommend much to do this for performance issues, another would be to adapt your update to predict a list and not a single value that is coming from the same variable you are doing.

Your Rigger would look something like this:

CREATE TRIGGER tgr_vendas_d
ON vendas
FOR DELETE
AS
BEGIN


  UPDATE c
     SET saldo_final = saldo_final - (SELECT SUM(d.valor) FROM deleted d WHERE d.data = c.caixa)
    FROM caixa c
   WHERE c.data  in (SELECT d.data FROM deleted d)
END
GO

  • It seems to me you forgot to remove SELECT from the original Rigger code. Even there is probably an error in the execution, by not declaring the variables @ date and @ value.

  • Thank you very much, I really forgot

1

A few weeks ago I’m writing articles on the pitfalls in the programming of procedures Trigger in SQL Server. The question of the procedure Trigger treat only one line is one of the most common cases. It is necessary to be aware that a procedure Trigger can be triggered to handle any, one or several lines.

Here’s a simple solution:

-- código #1
CREATE TRIGGER tgr_vendas_d
   ON vendas
   after DELETE as
BEGIN

-- encerra o processamento se não há linha para tratar
IF not exists (SELECT * from DELETED) return;

-- atualiza saldo de caixa
UPDATE C
  set saldo_final = C.saldo_final - D.valor
  from caixa as C
       inner join DELETED as D on D.data = C.data;

END;
GO

Browser other questions tagged database sql-server trigger

You are not signed in. Login or sign up in order to post.