> Triggers are fired per statament and NOT per row, you will always have to
> use a logic that will cover multi-rwo occurence.
>
> So the unstested version should be something like:
>
> UPDATE LOCALIZAPRODUTOS
> SET ESTOQUE= SUB1.SUM1 - SUB2.SUM2
> FROM LOCALIZAPRODUTOS L
> INNER JOIN DELETED D ON
> L.CODPRODUTO = D.CODPRODUTO
> AND L.CODEMPRESA = D.EMPRESA
> INNER JOIN
> ( SELECT ISNULL(SUM(ENTRADA)-SUM(SAÍDA),0) SUM2 FROM ESTOQUEMOVIMENTO
> WHERE LEFT(ENTSAI,1)='X') SUB1
> ON SUB1.CODPRODUTO = D.CODPRODUTO AND SUB1.EMPRESA=D.EMPRESA
> INNER JOIN
> (SELECT ISNULL(SUM(Qtdade),0) AS SUM2 FROM ESTOQUERESERVADO) SUB2
> ON SUB2.CODPRODUTO = D.CODPRODUTO AND SUB1.EMPRESA=D.EMPRESA
>
> Jens K Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
>
>
> "Frank Dulk" <fdulk@bol.com.br> wrote in message
> news:%2340SqTmbHHA.4476@TK2MSFTNGP03.phx.gbl...
>> In my system I have a table that records the stock movements and another
>> that records the balance of current stock of the product. The triggers
>> update the stock balance every time that movement exists in the stock
>> table. The trigger is the following:
>>
>> Create TRIGGER [DELETEESTOQUE] ON [EstoqueMovimento]
>> AFTER DELETE AS
>> BEGIN
>> DECLARE @EMPRESA [NUMERIC]
>> DECLARE @PRODUTO [NUMERIC]
>> DECLARE @ESTOQUE [MONEY]
>> SET @EMPRESA=(SELECT EMPRESA FROM DELETED)
>> SET @PRODUTO=(SELECT CODPRODUTO FROM DELETED)
>> SET @ESTOQUE=ISNULL((SELECT SUM(ENTRADA)-SUM(SAÍDA) FROM ESTOQUEMOVIMENTO
>> WHERE CODPRODUTO=@PRODUTO AND EMPRESA=@EMPRESA AND LEFT(ENTSAI,1)='X'),0)
>> SET @ESTOQUE=@ESTOQUE-ISNULL((SELECT SUM(Qtdade) FROM ESTOQUERESERVADO
>> WHERE CODPRODUTO=@PRODUTO AND EMPRESA=@EMPRESA),0)
>> UPDATE LOCALIZAPRODUTOS SET ESTOQUE=@ESTOQUE WHERE CODPRODUTO=@PRODUTO
>> AND CODEMPRESA=@EMPRESA
>> END
>>
>> The problem is when more than a records was excluded, gives mistake in
>> the instruction SET @EMPRESA=(SELECT EMPRESA FROM DELETED).
>>
>> How does to alter the trigger for her to execute him calculate
>> registration for registration of the table Deleted?
>>
>>
>

On Mon, 26 Mar 2007 21:52:31 -0300, Frank Dulk wrote:

>The logic is really what you passed in the answer, but SQL didn't accept the
>update. gave the following mistakes, that I didn't get to correct:
>
>Server: Msg 207, Level 16, State 3, Procedure DELETEESTOQUE, Line 3
>Invalid column name 'CODPRODUTO'.
>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>Invalid column name 'EMPRESA'.
>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>Invalid column name 'CODPRODUTO'.
>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>Invalid column name 'EMPRESA'.

> On Mon, 26 Mar 2007 21:52:31 -0300, Frank Dulk wrote:
>
>>The logic is really what you passed in the answer, but SQL didn't accept
>>the
>>update. gave the following mistakes, that I didn't get to correct:
>>
>>Server: Msg 207, Level 16, State 3, Procedure DELETEESTOQUE, Line 3
>>Invalid column name 'CODPRODUTO'.
>>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>>Invalid column name 'EMPRESA'.
>>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>>Invalid column name 'CODPRODUTO'.
>>Server: Msg 207, Level 16, State 1, Procedure DELETEESTOQUE, Line 3
>>Invalid column name 'EMPRESA'.
>
> Hi Frank,
>
> That means that you have misspelled either the table or (more probable)
> the column names.
>
> Could you post the CREATE TABLE statement used to create the table and
> the exact code of the trigger? Also, see www.aspfaq.com/5006.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis