帮忙看看这个触发器，急呀

[phaqyxiao]

CREATE TRIGGER insertdetail ON [dbo].[m_group]
FOR Insert
AS
declare @pid int,@encashcode varchar(50),@productcode varchar(50),@gid int,@pcount int,@gidstr varchar(9)
declare @codelen int,@codelenstr varchar(1),@opstr varchar(500),@i int,@pidstr varchar(9)
select @pcount=i.productcount,@pid=i.productid,@gid=i.id
from inserted i,m_group d where i.productid=d.productid
Select @encashcode=smsproductcode,@productcode=productcode from m_product where productid=@pid
set @gidstr=@gid
set @pidstr=cast(@pid as varchar(9))
if @pcount>0
begin
/**********************/
declare @maxsequence int
declare @exestr varchar(500),@maxsequencestr varchar(9)
set @exestr='declare @maxsequence int select @maxsequence=isnull(max(productsequence),0) from detail_'+@pidstr
exec(@exestr)
if @maxsequence is null
set @maxsequence=0
/***************************/
set @codelen=len(@encashcode)
set @codelenstr=@codelen
set @maxsequence=@maxsequence+1
set @i=0
while @i<@pcount
begin
set @maxsequencestr=@maxsequence
set @opstr='insert detail_'+@pidstr +' values(' +@pidstr + ','''+@maxsequencestr+''','''+@codelenstr +@encashcode+@maxsequencestr+''','''+@productcode+''',1,null,null,1,'+@gidstr+')'
--print @opstr
exec(@opstr)
set @i=@i+1
set @maxsequence=@maxsequence+1
end
end
在两段注释中取max中得不到所要得最大值。

[pengdali]

CREATE TRIGGER insertdetail ON [dbo].[m_group]
FOR Insert
AS
declare @pid int,@encashcode varchar(50),@productcode varchar(50),@gid int,@pcount int,@gidstr varchar(9)
declare @codelen int,@codelenstr varchar(1),@opstr varchar(500),@i int,@pidstr varchar(9)
select @pcount=i.productcount,@pid=i.productid,@gid=i.id
from inserted i,m_group d where i.productid=d.productid
Select @encashcode=smsproductcode,@productcode=productcode from m_product where productid=@pid
set @gidstr=@gid
set @pidstr=cast(@pid as varchar(9))
if @pcount>0
begin
/**********************/
declare @maxsequence int
declare @exestr nvarchar(500),@maxsequencestr varchar(9)
set @exestr=N'select @maxsequence=isnull(max(productsequence),0) from detail_'+@pidstr
exec sp_executesql @exestr,N'@maxsequence int output',@maxsequence output
if @maxsequence is null
set @maxsequence=0
/***************************/
set @codelen=len(@encashcode)
set @codelenstr=@codelen
set @maxsequence=@maxsequence+1
set @i=0
while @i<@pcount
begin
set @maxsequencestr=@maxsequence
set @opstr='insert detail_'+@pidstr +' values(' +@pidstr + ','''+@maxsequencestr+''','''+@codelenstr +@encashcode+@maxsequencestr+''','''+@productcode+''',1,null,null,1,'+@gidstr+')'
--print @opstr
exec(@opstr)
set @i=@i+1
set @maxsequence=@maxsequence+1
end
end

[pengdali]

关键：

/**********************/
declare @maxsequence int
declare @exestr nvarchar(500),@maxsequencestr varchar(9)
set @exestr=N'select @maxsequence=isnull(max(productsequence),0) from detail_'+@pidstr
exec sp_executesql @exestr,N'@maxsequence int output',@maxsequence output
if @maxsequence is null
set @maxsequence=0
/***************************/

[qdubit]

赞同二楼的做法，请楼主试试吧。

[txlicenhe]

1: /**********************/
declare @maxsequence int
declare @exestr nvarchar(500),@maxsequencestr varchar(9)
set @exestr=N'select @maxsequence=isnull(max(productsequence),0) from detail_'+@pidstr
exec sp_executesql @exestr,N'@maxsequence int output',@maxsequence output
if @maxsequence is null
set @maxsequence=0
/***************************/
2:
http://expert.csdn.net/Expert/topic/2364/2364046.xml?temp=.829693
[交流]动态SQL语句

[phaqyxiao]

谢谢以上几位的帮忙，我按pengdali的方法解决了。