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
例子