CREATE trigger tdb_tl_stkoutitem on dbo.tl_stkoutitem for delete as
begin
declare @numrows integer,
@numnull integer,
@stockno varchar(20),
@stockname varchar(60),
@stock varchar(20),
@stockstation varchar(20),
@brandno varchar(20),
@identityno varchar(20),
@carmodel varchar(20),
@qty decimal(12,2),
@price decimal(12,2),
@kcqty decimal(12,2),
@kcprice decimal(12,2),
@flag varchar(10)
select @flag=menu_id from saft_button where button_name='trigger_flag'
if @flag='A' return
select @stockno=m1.stockno, @stock=m1.stock, @stockstation=m1.stockstation,
@stockstation=m1.stockstation, @carmodel=m1.carmodel,
@identityno=m1.identityno, @brandno=m1.brandno,
@qty=m1.qty, @price=m1.price from deleted as m1
if @stock = "" return
select @kcqty=q1.qty, @kcprice=q1.price from tl_stock as q1
where q1.stock= @stock and q1.stockno= @stockno and q1.stockstation= @stockstation and q1.brandno= @brandno
/*如果库存表中不存在这种配件,则在库存表中插入一条新记录,否则自动累加库存数量,更新最新进价、批发价和统计平均进价*/
if not exists(select q1.stockno from tl_stock as q1
where q1.stockno= @stockno and q1.stock= @stock and q1.stockstation= @stockstation and q1.brandno= @brandno)
return
else
if(@kcqty+@qty)=0
begin
update tl_stock
set qty=0
where stockno= @stockno and stock= @stock and stockstation= @stockstation and brandno = @brandno
end
else
begin
update tl_stock
set qty=Isnull(qty,0)+Isnull(@qty,0)
where stockno= @stockno and stock= @stock and stockstation= @stockstation and brandno = @brandno
end
end