Frank Dulk
3/24/2007 10:33:00 PM
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?