我做了一个游标操作
单独运行的时候,是没有问题的
但是把那一段代码放到触发器中,就会有一个报错信息:
消息 16958,级别 16,状态 3,过程 notifyinfo,第 20 行
无法完成游标操作,因为在声明该游标后,所设置的选项发生了更改。
语句已终止这是为什么啊
急啊
单独运行的时候,是没有问题的
但是把那一段代码放到触发器中,就会有一个报错信息:
消息 16958,级别 16,状态 3,过程 notifyinfo,第 20 行
无法完成游标操作,因为在声明该游标后,所设置的选项发生了更改。
语句已终止这是为什么啊
急啊
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=1open 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 求高人指点
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=1open 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 红字部份发生了冲突
这个和我的插入语句有关系么?
insert into messageitemsnow values(1,123,123,123,'''ccc'',''cc''',1 )
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
列名 'flag' 无效。
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
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+''')'
问题我已经找到啦。
exec(@SQL)运行的问题。
嗯结贴