触发器中使用游标

[shaoka]

我做了一个游标操作
单独运行的时候，是没有问题的
但是把那一段代码放到触发器中，就会有一个报错信息：
消息 16958，级别 16，状态 3，过程 notifyinfo，第 20 行
无法完成游标操作，因为在声明该游标后，所设置的选项发生了更改。
语句已终止

这是为什么啊。。。
急啊。。。

[htl258_Tony]

代码在哪？

[shaoka]

if object_id('dbo.notifyinfo','tr') is not null
drop trigger dbo.notifyinfo
go
create trigger notifyinfo
on dbo.messageitemsnow
after insert
as
begin
declare @msgid int
declare @note varchar(max)
declare @sendto varchar(max)
declare @tel nchar(11)
declare @SQL Varchar(2000)

declare H_settle Cursor keyset for
select msgid,mstype+':'+msnote+msend note,sendto from messageitemsnow where flag=1

open h_settle
fetch next from h_settle into @msgid,@note,@sendto
while @@fetch_status=0 begin
set @SQL='declare H_t cursor for select Ltel from customsname where customersname in ('+ @sendto+')'
exec(@SQL)
open h_t
fetch next from h_t into @tel
while(@@fetch_status=0) begin
insert into to_ex_sms(teln,msg,sys_code,sys_name_id,sys_subname_id,msgid) values(@tel,@note,'0','0','0020',@msgid)
fetch next from h_t into @tel
end
close h_t
deallocate h_t
update messageitemsnow set flag=0 where msgid=@msgid
fetch next from h_settle into @msgid,@note,@sendto
end
close h_settle
deallocate h_settle
end

求高人指点。。。

[htl258_Tony]

if object_id('dbo.notifyinfo','tr') is not null
drop trigger dbo.notifyinfo
go
create trigger notifyinfo
on dbo.messageitemsnow
after insert
as
begin
declare @msgid int
declare @note varchar(max)
declare @sendto varchar(max)
declare @tel nchar(11)
declare @SQL Varchar(2000)

declare H_settle Cursor keyset for
select msgid,mstype+':'+msnote+msend note,sendto from messageitemsnow where flag=1

open h_settle
fetch next from h_settle into @msgid,@note,@sendto
while @@fetch_status=0 begin
set @SQL='declare H_t cursor for select Ltel from customsname where customersname in ('+ @sendto+')'
exec(@SQL)
open h_t
fetch next from h_t into @tel
while(@@fetch_status=0) begin
insert into to_ex_sms(teln,msg,sys_code,sys_name_id,sys_subname_id,msgid) values(@tel,@note,'0','0','0020',@msgid)
fetch next from h_t into @tel
end
close h_t
deallocate h_t
update messageitemsnow set flag=0 where msgid=@msgid
fetch next from h_settle into @msgid,@note,@sendto
end
close h_settle
deallocate h_settle
end

红字部份发生了冲突

[shaoka]

可是就是我把下面这一条去掉，也是不行的，还是一样的错误。
这个和我的插入语句有关系么？
insert into messageitemsnow values(1,123,123,123,'''ccc'',''cc''',1 )

[htl258_Tony]

[Quote=引用 4 楼 shaoka 的回复:]
可是就是我把下面这一条去掉，也是不行的，还是一样的错误。
这个和我的插入语句有关系么？
insert into messageitemsnow values(1,123,123,123,'''ccc'',''cc''',1 )
[/Quote]
有关系

[shaoka]

[Quote=引用 5 楼 htl258 的回复:]
引用 4 楼 shaoka 的回复:
可是就是我把下面这一条去掉，也是不行的，还是一样的错误。
这个和我的插入语句有关系么？
insert into messageitemsnow values(1,123,123,123,'''ccc'',''cc''',1 )

有关系
[/Quote]

那关系是啥呃。。。。

[htl258_Tony]

if object_id('dbo.notifyinfo','tr') is not null
drop trigger dbo.notifyinfo
go
create trigger notifyinfo
on dbo.messageitemsnow
after insert
as
begin
	declare @msgid int
	declare @note varchar(max)
	declare @sendto varchar(max)
	declare @tel nchar(11)
	declare @SQL Varchar(2000) 
	select msgid,mstype+':'+msnote+msend note,sendto into # from messageitemsnow where flag=1
	declare H_settle Cursor keyset for 
	select * from # where flag=1

	open h_settle
	fetch next from h_settle into @msgid,@note,@sendto
	while @@fetch_status=0 
	begin
		set @SQL='declare H_t cursor for select Ltel 
					from customsname where customersname in ('+ @sendto+')'
		exec(@SQL)
		open h_t
		fetch next from h_t into @tel
		while(@@fetch_status=0) 
		begin
			insert into to_ex_sms(teln,msg,sys_code,sys_name_id,sys_subname_id,msgid) 
				values(@tel,@note,'0','0','0020',@msgid)
			fetch next from h_t into @tel
		end 
		close h_t
		deallocate h_t 
		
		update messageitemsnow set flag=0 where msgid=@msgid
		fetch next from h_settle into @msgid,@note,@sendto
	end 
	close h_settle
	deallocate h_settle
	drop table #
end 

try

[shaoka]

消息 207，级别 16，状态 1，过程 notifyinfo，第 13 行
列名 'flag' 无效。

[htl258_Tony]

应该可以不需要游标，触发器用游标是最消耗性能的。

[htl258_Tony]

if object_id('dbo.notifyinfo','tr') is not null
drop trigger dbo.notifyinfo
go
create trigger notifyinfo
on dbo.messageitemsnow
after insert
as
begin
declare @msgid int
declare @note varchar(max)
declare @sendto varchar(max)
declare @tel nchar(11)
declare @SQL Varchar(2000)
select msgid,mstype+':'+msnote+msend note,sendto into # from messageitemsnow where flag=1
declare H_settle Cursor keyset for
select * from # -- where flag=1这里已经不需要了
open h_settle
fetch next from h_settle into @msgid,@note,@sendto
while @@fetch_status=0
begin
set @SQL='declare H_t cursor for select Ltel
from customsname where customersname in ('+ @sendto+')'
exec(@SQL)
open h_t
fetch next from h_t into @tel
while(@@fetch_status=0)
begin
insert into to_ex_sms(teln,msg,sys_code,sys_name_id,sys_subname_id,msgid)
values(@tel,@note,'0','0','0020',@msgid)
fetch next from h_t into @tel
end
close h_t
deallocate h_t

update messageitemsnow set flag=0 where msgid=@msgid
fetch next from h_settle into @msgid,@note,@sendto
end
close h_settle
deallocate h_settle
drop table #
end

[-狙击手-]

。。。

[shaoka]

嗯。。。我刚才就试过了。。。
如果去掉的话，就还是之前的那个错误。。

[shaoka]

[Quote=引用 8 楼 htl258 的回复:]
应该可以不需要游标，触发器用游标是最消耗性能的。
[/Quote]

因为我是要把一条记录分成多条，插到另外一个里面去，而且运算不会太大。

[shaoka]

寻个神仙来救命。。。

[yhtapmys]

set @SQL='declare H_t cursor for select Ltel from customsname where customersname in ('+ @sendto+')'

--你把它改成下面的试一试
set @SQL='declare H_t cursor for select Ltel from customsname where customersname in ('''+ @sendto+''')'

[shaoka]

嗯。。。感谢同志们的帮助。
问题我已经找到啦。
exec（@SQL）运行的问题。
嗯。。。结贴