SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[stock_update]
ON [dbo].[tbl_stock]
FOR UPDATE
AS
begin TRANSACTION
declare @nror int
set @nror=0
--if (update(statusid))
--begin
declare @id int
declare @old_number int
declare @number int
declare @statusid int
declare @old_statusid int
declare @old_stock int
declare new cursor local for select [id],[number],[statusid] from inserted
declare old cursor local for select [statusid] ,[number],[stockid] from deleted
open new
open old
fetch next from new into @id, @number,@statusid
fetch next from old into @old_statusid, @old_number,@old_stock
while @@fetch_status=0
begin
--select @id = id,@number=number,@statusid=statusid from inserted
--select @old_statusid=statusid,@old_number=number,@old_stock=stockid from Deleted
declare @product_no varchar(50)
declare @color varchar(50)
declare @product_size varchar(100)
declare @stockid int
select @product_no=product_no,@color=color,@product_size=product_size ,@stockid=stockid from tbl_stock where id=@id
if @old_statusid=2 and @statusid=1
--此处是在途库存确认收货步骤
begin
declare @count_num int
select @count_num=count(*) from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid=@stockid
if @count_num>1
begin
update tbl_stock set number=number+@number where product_no=@product_no and product_size=@product_size and color=@color and statusid=1 and stockid=@stockid
set @nror=@nror+@@error
delete tbl_stock where id=@id
set @nror=@nror+@@error
insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,@stockid,@product_no,@color,@product_size,@number,'在途确认','入库','') set @nror=@nror+@@error
end
else
begin
update tbl_stock set statusid='1' where id=@id
set @nror=@nror+@@error
insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,@stockid,@product_no,@color,@product_size,@number,'在途确认','入库','') set @nror=@nror+@@error
end
end
else if @old_statusid=3 and @statusid=1
--此处是采购确认收货
begin
declare @count_num_cai int
select @count_num_cai=count(*) from tbl_stock where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid='2'
if @count_num_cai>0
begin
update tbl_stock set number=number+@number where product_no=@product_no and color=@color and product_size=@product_size and statusid=1 and stockid='2'
set @nror=@nror+@@error
delete tbl_stock where id=@id
set @nror=@nror+@@error
insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,'2',@product_no,@color,@product_size,@number,'采购确认','入库','') set @nror=@nror+@@error
end
else
begin
update tbl_stock set statusid='1',stockid='2' where id=@id
set @nror=@nror+@@error
insert into tbl_stock_inout_record(statusid,stockid,product_no,color,product_size,number,operation,operation_type,users) values ('1' ,'2',@product_no,@color,@product_size,@number,'采购确认','入库','')
set @nror=@nror+@@error
end
end
fetch next from new into @id, @number,@statusid
fetch next from old into @old_statusid, @old_number,@old_stock
end
close old
close new
deallocate new
deallocate old
--end
if @nror<>0
begin
rollback transaction
end
else
begin
commit transaction
end
CREATE TRIGGER [equpdate] ON [dbo].[equipment] FOR UPDATE AS declare @oldbh char(10),@newbh char(10) declare new cursor local for select num from inserted declare old cursor local for select num from deleted open new open old fetch next from new into @newbh fetch next from old into @oldbh while @@fetch_status=0 begin if @oldbh <> @newbh update server_t set num=@newbh where num=@oldbh fetch next from new into @newbh fetch next from old into @oldbh end close old close new deallocate new deallocate old go
例子