to kingswang:
库存设计结构不同,统计数据不同,SP也会不同的,客户一般会看流水账、主帐,流水帐的修改
会随时影响主帐,数据直接从主帐查就行了。这是我三年前些的流水帐的触发器,跟SP
没有什么区别,你可以看看,也希望你能学习一下触发器、过程
CREATE TRIGGER [ImlistInsert] ON [Imlist]
FOR INSERT
AS
DECLARE @TranName VARCHAR(48), @State VARCHAR(48)
DECLARE @DateC VARCHAR(48), @HomeC VARCHAR(48), @ItemC VARCHAR(48), @InOut VARCHAR(48), @Quant FLOAT
SET @DateC = '19730309'; SELECT @TranName = 'TranImlist'
BEGIN TRANSACTION @TranName
SELECT @State = State FROM Config WHERE ItemC = 'IM01'
SELECT @HomeC = HomeC, @ItemC = ItemC, @Quant = Quant, @InOut = InOut FROM Inserted
IF (SELECT DateC FROM Immain WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC) IS NULL
INSERT INTO Immain (DateC, HomeC, ItemC) VALUES ('19730309', @HomeC, @ItemC)
--现有库存数量
IF SUBSTRING(@InOut, 1, 1) = '+'
BEGIN
UPDATE Dmitem SET QuanT = QuanT + @Quant WHERE ItemC = @ItemC
UPDATE Immain SET QuanT = QuanT + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
END ELSE
IF SUBSTRING(@InOut, 1, 1) = '-'
BEGIN
UPDATE Dmitem SET QuanT = QuanT - @Quant WHERE ItemC = @ItemC
UPDATE Immain SET QuanT = QuanT - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
END
IF (@State = '执行') AND ((SELECT QuanT FROM Dmitem WHERE ItemC = @ItemC) < 0 OR (SELECT QuanT FROM Immain WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC) < 0)
BEGIN
RAISERROR('库存数量不能<0,请重新输入!', 15, 1)
ROLLBACK TRANSACTION @TranName
END
--已分配量数量
IF SUBSTRING(@InOut, 2, 1) = '+'
UPDATE Dmitem SET QuanA = QuanA + @Quant WHERE ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 2, 1) = '-'
UPDATE Dmitem SET QuanA = QuanA - @Quant WHERE ItemC = @ItemC
--累计入库数量
IF SUBSTRING(@InOut, 3, 1) = '+'
UPDATE Immain SET MIQua = MIQua + @Quant, YIQua = YIQua + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 3, 1) = '-'
UPDATE Immain SET MIQua = MIQua - @Quant, YIQua = YIQua - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--累计出库数量
IF SUBSTRING(@InOut, 4, 1) = '+'
UPDATE Immain SET MOQua = MOQua + @Quant, YOQua = YOQua + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 4, 1) = '-'
UPDATE Immain SET MOQua = MOQua - @Quant, YOQua = YOQua - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--采购待验数量
IF SUBSTRING(@InOut, 5, 1) = '+'
UPDATE Immain SET QuanC = QuanC + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 5, 1) = '-'
UPDATE Immain SET QuanC = QuanC - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--不合格品数量
IF SUBSTRING(@InOut, 6, 1) = '+'
UPDATE Immain SET QuanF = QuanF + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 6, 1) = '-'
UPDATE Immain SET QuanF = QuanF - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--借 入数量
IF SUBSTRING(@InOut,11, 1) = '+'
UPDATE Immain SET QuanL = QuanL + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut,11, 1) = '-'
UPDATE Immain SET QuanL = QuanL - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--借 出数量
IF SUBSTRING(@InOut,12, 1) = '+'
UPDATE Immain SET QuanB = QuanB + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut,12, 1) = '-'
UPDATE Immain SET QuanB = QuanB - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--车间领用数量
IF SUBSTRING(@InOut, 7, 1) = '+'
UPDATE Immain SET WorkI = WorkI + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 7, 1) = '-'
UPDATE Immain SET WorkI = WorkI - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--车间生产数量
IF SUBSTRING(@InOut, 8, 1) = '+'
UPDATE Immain SET WorkO = WorkO + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 8, 1) = '-'
UPDATE Immain SET WorkO = WorkO - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--采购入库数量
IF SUBSTRING(@InOut, 9, 1) = '+'
UPDATE Immain SET QuanP = QuanP + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut, 9, 1) = '-'
UPDATE Immain SET QuanP = QuanP - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
--销售出库数量
IF SUBSTRING(@InOut,10, 1) = '+'
UPDATE Immain SET QuanS = QuanS + @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC ELSE
IF SUBSTRING(@InOut,10, 1) = '-'
UPDATE Immain SET QuanS = QuanS - @Quant WHERE DateC = @DateC AND HomeC = @HomeC AND ItemC = @ItemC
COMMIT TRANSACTION @TranName