lyfz_repos/经典版源码/document/数据库维护脚本/dindansp取件ok触发器.sql

--insert类型触发器;
if (object_id('tgr_dindansp_insert','tr') is not null)
	drop trigger tgr_dindansp_insert

go
create trigger tgr_dindansp_insert on dindansp for insert--订单表更新数据时;
as
begin transaction
	declare @order nvarchar(24)			--订单号;
	declare @spid nvarchar(64)			--商品id;
	--获取update表更新的dindansp数据;
	select @order = id, @spid = spid from inserted
	if (@spid is not null and @spid <> '')
		update dindan set [status3] = '未取' where [id] = @order
commit transaction
go

--update类型触发器;
if (object_id('tgr_dindansp_update','tr') is not null)
	drop trigger tgr_dindansp_update

go
create trigger tgr_dindansp_update on dindansp for update--订单表更新数据时;
as
begin transaction
	declare @order nvarchar(24)			--订单号;
	declare @countAll int				--订单所有产品数量;
	declare @countOK  int				--订单取件OK的产品数量;

	--获取update表更新的dindansp数据;
	select @order = id from inserted
	select @countAll=count(*) from dindansp where [id] = @order
	select @countOK=count(*) from dindansp where [id] = @order and [status2] = 'OK' and ( spid is not null and spid <> '')

	if ( @countAll = @countOK )
		update dindan set [status3] = 'OK' where [id] = @order
	else
		update dindan set [status3] = '未取' where [id] = @order
commit transaction
go

--delete类型触发器;
if (object_id('tgr_dindansp_delete','tr') is not null)
	drop trigger tgr_dindansp_delete

go
create trigger tgr_dindansp_delete on dindansp for delete--订单表更新数据时;
as
begin transaction
	declare @order nvarchar(24)			--订单号;
	declare @countAll int				--订单所有产品数量;
	declare @countOK  int				--订单取件OK的产品数量;

	--获取update表更新的dindansp数据;
	select @order = id from deleted
	select @countAll=count(*) from dindansp where [id] = @order
	select @countOK=count(*) from dindansp where [id] = @order and [status2] = 'OK' and ( spid is not null and spid <> '')

	if ( @countAll = @countOK )
		update dindan set [status3] = 'OK' where [id] = @order
commit transaction
go


--更新dindansp表,使之更新dindan表;
select identity(int,1,1) as aid, autoid, zs into #temp1 from dindansp
declare @maxid	int
declare @id		nvarchar(50)
declare @zs		nvarchar(20)

select @maxid = max(aid) from #temp1

while @maxid > 0
begin
	select @id = autoid, @zs = zs from #temp1 where aid = @maxid
	update dindansp set zs = @zs where autoid = @id
	set @maxid = @maxid -1
end
drop table #temp1