If Exists(Select * From Inserted) Begin DECLARE csSellDataClassInsert CURSOR FOR SELECT FactDate, ShopID, LEFT(ClassID, 3) AS ClassIDb, LEFT(ClassID, 4) AS ClassIDm, ClassID AS ClassIDs, SUM(Amount) AS Amount, SUM(SellMoney) AS SellMoney, SUM(CostMoney) AS CostMoney FROM Inserted GROUP BY FactDate, ShopID, ClassID, LEFT(ClassID, 3), LEFT(ClassID, 4) ORDER BY FactDate, ShopID, ClassID OPEN csSellDataClassInsert
FETCH csSellDataClassInsert INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney WHILE @@FETCH_STATUS=0 BEGIN If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDb) UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDb) Else INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDb, @sClassIDs, 1, @fAmount, @cSellMoney, @cCostMoney)
If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDm) UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDm) Else INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDm, @sClassIDs, 2, @fAmount, @cSellMoney, @cCostMoney)
If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDs) UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDs) Else INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDs, @sClassIDs, 3, @fAmount, @cSellMoney, @cCostMoney)
FETCH NEXT FROM csSellDataClassInsert INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney END CLOSE csSellDataClassInsert DEALLOCATE csSellDataClassInsert End Else Begin DECLARE csSellDataClassDelete CURSOR FOR SELECT FactDate, ShopID, LEFT(ClassID, 3) AS ClassIDb, LEFT(ClassID, 4) AS ClassIDm, ClassID AS ClassIDs, SUM(Amount) AS Amount, SUM(SellMoney) AS SellMoney, SUM(CostMoney) AS CostMoney FROM Deleted GROUP BY FactDate, ShopID, ClassID, LEFT(ClassID, 3), LEFT(ClassID, 4) ORDER BY FactDate, ShopID, ClassID OPEN csSellDataClassDelete
FETCH csSellDataClassDelete INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney WHILE @@FETCH_STATUS=0 BEGIN DELETE FROM SellDataDetailClassFrame WHERE FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDs
UPDATE SellDataDetailClassFrame SET Amount = Amount - @fAmount, SellMoney = SellMoney - @cSellMoney, CostMoney = CostMoney - @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDm) DELETE FROM SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDm AND Amount = 0 AND SellMoney = 0 AND CostMoney = 0
UPDATE SellDataDetailClassFrame SET Amount = Amount - @fAmount, SellMoney = SellMoney - @cSellMoney, CostMoney = CostMoney - @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDb) DELETE FROM SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDb AND Amount = 0 AND SellMoney = 0 AND CostMoney = 0
FETCH NEXT FROM csSellDataClassDelete INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney END CLOSE csSellDataClassDelete DEALLOCATE csSellDataClassDelete End'')' Execute sp_executesql @sSql
把触发器改为3个! Create Trigger SellDataDetailSupplyClass_Insert_Delete On dbo.SellDataDetailSupplyClass For Insert AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete On dbo.SellDataDetailSupplyClass For Delete AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete On dbo.SellDataDetailSupplyClass For Update As或者2个Create Trigger SellDataDetailSupplyClass_Insert_Delete On dbo.SellDataDetailSupplyClass For Insert, Delete AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete On dbo.SellDataDetailSupplyClass For Update As
Select @sSql = 'Use ' + Rtrim(@DataBaseName) + ' Exec(''Create Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Insert, Delete
As
DECLARE @datFactDate DATETIME
DECLARE @sShopID CHAR(4)
DECLARE @sClassIDb Char(3)
DECLARE @sClassIDm Char(4)
DECLARE @sClassIDs Char(5)
DECLARE @fAmount Float
DECLARE @cSellMoney Money
DECLARE @cCostMoney Money
If Exists(Select * From Inserted)
Begin
DECLARE csSellDataClassInsert CURSOR FOR
SELECT FactDate, ShopID, LEFT(ClassID, 3) AS ClassIDb, LEFT(ClassID, 4) AS ClassIDm, ClassID AS ClassIDs, SUM(Amount) AS Amount, SUM(SellMoney) AS SellMoney, SUM(CostMoney) AS CostMoney FROM Inserted GROUP BY FactDate, ShopID, ClassID, LEFT(ClassID, 3), LEFT(ClassID, 4) ORDER BY FactDate, ShopID, ClassID
OPEN csSellDataClassInsert
FETCH csSellDataClassInsert INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney
WHILE @@FETCH_STATUS=0
BEGIN
If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDb)
UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDb)
Else
INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDb, @sClassIDs, 1, @fAmount, @cSellMoney, @cCostMoney)
If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDm)
UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDm)
Else
INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDm, @sClassIDs, 2, @fAmount, @cSellMoney, @cCostMoney)
If Exists(Select * From SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDs)
UPDATE SellDataDetailClassFrame SET Amount = Amount + @fAmount, SellMoney = SellMoney + @cSellMoney, CostMoney = CostMoney + @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDs)
Else
INSERT INTO SellDataDetailClassFrame (FactDate, ShopID, ClassID, ClassState, ClassOrder, Amount, SellMoney, CostMoney) VALUES (@datFactDate, @sShopID, @sClassIDs, @sClassIDs, 3, @fAmount, @cSellMoney, @cCostMoney)
FETCH NEXT FROM csSellDataClassInsert INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney
END
CLOSE csSellDataClassInsert
DEALLOCATE csSellDataClassInsert
End
Else
Begin
DECLARE csSellDataClassDelete CURSOR FOR
SELECT FactDate, ShopID, LEFT(ClassID, 3) AS ClassIDb, LEFT(ClassID, 4) AS ClassIDm, ClassID AS ClassIDs, SUM(Amount) AS Amount, SUM(SellMoney) AS SellMoney, SUM(CostMoney) AS CostMoney
FROM Deleted
GROUP BY FactDate, ShopID, ClassID, LEFT(ClassID, 3), LEFT(ClassID, 4)
ORDER BY FactDate, ShopID, ClassID
OPEN csSellDataClassDelete
FETCH csSellDataClassDelete INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM SellDataDetailClassFrame WHERE FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDs
UPDATE SellDataDetailClassFrame SET Amount = Amount - @fAmount, SellMoney = SellMoney - @cSellMoney, CostMoney = CostMoney - @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDm)
DELETE FROM SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDm AND Amount = 0 AND SellMoney = 0 AND CostMoney = 0
UPDATE SellDataDetailClassFrame SET Amount = Amount - @fAmount, SellMoney = SellMoney - @cSellMoney, CostMoney = CostMoney - @cCostMoney WHERE (FactDate = @datFactDate) AND (ShopID = @sShopID) AND (ClassID = @sClassIDb)
DELETE FROM SellDataDetailClassFrame Where FactDate = @datFactDate AND ShopID = @sShopID AND ClassID = @sClassIDb AND Amount = 0 AND SellMoney = 0 AND CostMoney = 0
FETCH NEXT FROM csSellDataClassDelete INTO @datFactDate, @sShopID, @sClassIDb, @sClassIDm, @sClassIDs, @fAmount, @cSellMoney, @cCostMoney
END
CLOSE csSellDataClassDelete
DEALLOCATE csSellDataClassDelete
End'')' Execute sp_executesql @sSql
hilite2000(大尾巴狼)
yangwq99(yangwq)等等朋友,如果是你们,你们会怎么弄?
Create Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Insert
AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Delete
AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Update
As或者2个Create Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Insert, Delete
AsCreate Trigger SellDataDetailSupplyClass_Insert_Delete
On dbo.SellDataDetailSupplyClass
For Update
As