我有一个为插入而创建的触发器函数。我想在StockItem上显示更改,并且代码中有两个stockTransaction和StokItem,我成功了。但是现在我只需更改我的Item表的BuyinPrice列,这是更新StokItem表后的第三个表。
这是我的代码:
ALTER TRIGGER [dbo].[StockTransactionInserted]
ON [dbo].[StockTransaction]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@IdItem int,
@IdStorage int,
@TransactionType char(1),
@Code char(1),
@Quantity decimal(18, 8),
@UnitPrice decimal(18, 8),
@Discount decimal(18, 8),
@FatAltDiscount decimal(18, 8),
@VAT decimal(18, 8)
SELECT
@IdItem = IdItem,
@IdStorage = IdStorage,
@TransactionType = TransactionType,
@Code = Code,
@Quantity = Quantity,
@UnitPrice = UnitPrice,
@Discount = Discount,
@FatAltDiscount = FatAltDiscount,
@VAT = VAT
FROM inserted
IF NOT EXISTS (SELECT * FROM StockItem WHERE IdItem = @IdItem AND IdStorage = @IdStorage)
BEGIN
INSERT INTO StockItem (IdItem, IdStorage, TOP_GIRIS_MIK, TOP_CIKIS_MIK)
VALUES (@IdItem, @IdStorage, 0, 0)
END
UPDATE StockItem
SET
TOP_GIRIS_MIK = TOP_GIRIS_MIK + CASE WHEN @Code = 'G' THEN @Quantity ELSE 0 END,
TOP_GIRIS_TUT = TOP_GIRIS_TUT + CASE WHEN @Code = 'G' THEN (@UnitPrice * (1- @Discount/100)) * @Quantity ELSE 0 END,
TOP_CIKIS_MIK = TOP_CIKIS_MIK + CASE WHEN @Code = 'C' THEN @Quantity ELSE 0 END,
TOP_CIKIS_TUT = TOP_CIKIS_TUT + CASE WHEN @Code = 'C' THEN (@UnitPrice * (1- @Discount/100)) * @Quantity ELSE 0 END,
ORT_BR_FIAT = CASE WHEN ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) + @Quantity) = 0 THEN (ORT_BR_FIAT + (@UnitPrice * (1- @Discount/100))) / 2 ELSE ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) * ORT_BR_FIAT + (@Quantity * (@UnitPrice * (1- @Discount/100)))) / ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) + @Quantity) END,
SON_GIR_BR_FIAT = (CASE WHEN @UnitPrice=0 OR @TransactionType != 'A' THEN SON_GIR_BR_FIAT ELSE @UnitPrice * (1- @Discount/100) END),
SON_GIR_NET_FIAT = (CASE WHEN @UnitPrice=0 OR @TransactionType != 'A' THEN SON_GIR_NET_FIAT ELSE (@UnitPrice * (1- @Discount/100)) + (@UnitPrice * @VAT/100) END)
WHERE IdItem = @IdItem AND IdStorage = @IdStorage
UPDATE Item
SET BuyingPrice = StockItem.SON_GIR_BR_FIAT where item_id = @IdItem
END
其实更新StokItem正常工作不是问题,但是当我想在这里更新Item表时
UPDATE Item
SET BuyingPrice = StockItem.SON_GIR_BR_FIAT where item_id = @IdItem
它对StockItem.SON_GIR_BR_FIAT列显示多标识符错误。
你能帮我么!谢谢!
现在,它应该可以工作了!插入的表可以包含0、1或多行。因此,您需要对受影响的行使用CURSOR,以便逐行更新表StockItem和Item。
ALTER TRIGGER [dbo].[StockTransactionInserted]
ON [dbo].[StockTransaction]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@IdItem int,
@IdStorage int,
@TransactionType char(1),
@Code char(1),
@Quantity decimal(18, 8),
@UnitPrice decimal(18, 8),
@Discount decimal(18, 8),
@FatAltDiscount decimal(18, 8),
@VAT decimal(18, 8)
DECLARE curStockTransaction CURSOR FOR
SELECT IdItem, IdStorage, TransactionType, Code, Quantity, UnitPrice, Discount, FatAltDiscount, VAT
FROM inserted
OPEN curStockTransaction
FETCH NEXT FROM curStockTransaction INTO @IdItem, @IdStorage, @TransactionType, @Code, @Quantity, @UnitPrice, @Discount, @FatAltDiscount, @VAT
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM StockItem WHERE IdItem = @IdItem AND IdStorage = @IdStorage)
BEGIN
INSERT INTO StockItem (IdItem, IdStorage, TOP_GIRIS_MIK, TOP_CIKIS_MIK)
VALUES (@IdItem, @IdStorage, 0, 0)
END
UPDATE StockItem
SET
TOP_GIRIS_MIK = TOP_GIRIS_MIK + CASE WHEN @Code = 'G' THEN @Quantity ELSE 0 END,
TOP_GIRIS_TUT = TOP_GIRIS_TUT + CASE WHEN @Code = 'G' THEN (@UnitPrice * (1- @Discount/100)) * @Quantity ELSE 0 END,
TOP_CIKIS_MIK = TOP_CIKIS_MIK + CASE WHEN @Code = 'C' THEN @Quantity ELSE 0 END,
TOP_CIKIS_TUT = TOP_CIKIS_TUT + CASE WHEN @Code = 'C' THEN (@UnitPrice * (1- @Discount/100)) * @Quantity ELSE 0 END,
ORT_BR_FIAT = CASE WHEN ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) + @Quantity) = 0 THEN (ORT_BR_FIAT + (@UnitPrice * (1- @Discount/100))) / 2 ELSE ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) * ORT_BR_FIAT + (@Quantity * (@UnitPrice * (1- @Discount/100)))) / ((TOP_GIRIS_MIK - TOP_CIKIS_MIK) + @Quantity) END,
SON_GIR_BR_FIAT = (CASE WHEN @UnitPrice=0 OR @TransactionType != 'A' THEN SON_GIR_BR_FIAT ELSE @UnitPrice * (1- @Discount/100) END),
SON_GIR_NET_FIAT = (CASE WHEN @UnitPrice=0 OR @TransactionType != 'A' THEN SON_GIR_NET_FIAT ELSE (@UnitPrice * (1- @Discount/100)) + (@UnitPrice * @VAT/100) END)
WHERE
IdItem = @IdItem AND IdStorage = @IdStorage
UPDATE Item
SET
BuyingPrice = StockItem.SON_GIR_BR_FIAT
FROM Item
JOIN StockItem ON (StockItem.IdItem = Item.item_id)
where
item_id = @IdItem
FETCH NEXT FROM curStockTransaction INTO @IdItem, @IdStorage, @TransactionType, @Code, @Quantity, @UnitPrice, @Discount, @FatAltDiscount, @VAT
END
CLOSE curStockTransaction
DEALLOCATE curStockTransaction
END
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句