有个表m_shortmsg ,当记录被更新时(有可能一次更新一条或多条),要触发一个触发器,这个触发器要将本次被更新的记录写到另一个表msg_mt中,我写了一个,但当我更新m_shortmsg 表时提示"游标未打开",请各位帮忙看看错在什么地方,这样写对否:
USE [shortMsg_生产]
GO
/****** 对象: Trigger [dbo].[tg_insert_m_shortmsg] 脚本日期: 07/08/2011 17:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_insert_m_shortmsg] ON [dbo].[m_shortmsg]
FOR UPDATE
AS
DECLARE @FMsgID int
DECLARE @FRcvSendType char(1)
DECLARE @FMsgType char(2)
DECLARE @FOperator char(10)
DECLARE @FCrtDateTime datetime
DECLARE @FRealSndDateTime char(25)
DECLARE @FState char(1)
DECLARE @FCustomerNo Nvarchar(2000)
DECLARE @FTelNo varchar(1500)
DECLARE @FTelCount int
DECLARE @FMsg varchar(5000)
DECLARE @FExFl char(1)
DECLARE @MsgCount int
if update(Fstate)
begin
DECLARE msg_cursor CURSOR Scroll
FOR
SELECT FMsgID,FRcvSendType,FMsgType,FOperator,
FCrtDateTime,FRealSndDateTime,FState,FCustomerNo,
FTelNo,FTelCount,FMsg,FExFl FROM INSERTED
OPEN msg_cursor
FETCH NEXT FROM msg_cursor INTO @FMsgID,@FRcvSendType,@FMsgType,@FOperator,
@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,
@FTelNo,@FTelCount,@FMsg,@FExFl
WHILE @@fetch_status=0
BEGIN
insert msg_mt(SM_ID,FRcvSendType,FMsgType,FOperator,FCrtDateTime,FRealSndDateTime,FState,FCustomerNo,Mobiles,FTelCount,Content,FExFl)
values(@FMsgID,@FRcvSendType,@FMsgType,@FOperator,@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,@FTelNo,@FTelCount,@FMsg,@FExFl)
END
CLOSE msg_cursor
DealLocate msg_cursor
end
USE [shortMsg_生产]
GO
/****** 对象: Trigger [dbo].[tg_insert_m_shortmsg] 脚本日期: 07/08/2011 17:10:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_insert_m_shortmsg] ON [dbo].[m_shortmsg]
FOR UPDATE
AS
DECLARE @FMsgID int
DECLARE @FRcvSendType char(1)
DECLARE @FMsgType char(2)
DECLARE @FOperator char(10)
DECLARE @FCrtDateTime datetime
DECLARE @FRealSndDateTime char(25)
DECLARE @FState char(1)
DECLARE @FCustomerNo Nvarchar(2000)
DECLARE @FTelNo varchar(1500)
DECLARE @FTelCount int
DECLARE @FMsg varchar(5000)
DECLARE @FExFl char(1)
DECLARE @MsgCount int
if update(Fstate)
begin
DECLARE msg_cursor CURSOR Scroll
FOR
SELECT FMsgID,FRcvSendType,FMsgType,FOperator,
FCrtDateTime,FRealSndDateTime,FState,FCustomerNo,
FTelNo,FTelCount,FMsg,FExFl FROM INSERTED
OPEN msg_cursor
FETCH NEXT FROM msg_cursor INTO @FMsgID,@FRcvSendType,@FMsgType,@FOperator,
@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,
@FTelNo,@FTelCount,@FMsg,@FExFl
WHILE @@fetch_status=0
BEGIN
insert msg_mt(SM_ID,FRcvSendType,FMsgType,FOperator,FCrtDateTime,FRealSndDateTime,FState,FCustomerNo,Mobiles,FTelCount,Content,FExFl)
values(@FMsgID,@FRcvSendType,@FMsgType,@FOperator,@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,@FTelNo,@FTelCount,@FMsg,@FExFl)
END
CLOSE msg_cursor
DealLocate msg_cursor
end
select ,,,,,,,,,, from inserted别用游标 用这个试试
BEGIN
insert msg_mt(SM_ID,FRcvSendType,FMsgType,FOperator,FCrtDateTime,FRealSndDateTime,FState,FCustomerNo,Mobiles,FTelCount,Content,FExFl)
values(@FMsgID,@FRcvSendType,@FMsgType,@FOperator,@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,@FTelNo,@FTelCount,@FMsg,@FExFl)
FETCH NEXT FROM msg_cursor INTO @FMsgID,@FRcvSendType,@FMsgType,@FOperator,@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,@FTelNo,@FTelCount,@FMsg,@FExFlEND這里少了一段
FETCH NEXT FROM msg_cursor INTO @FMsgID,@FRcvSendType,@FMsgType,@FOperator,
@FCrtDateTime,@FRealSndDateTime,@FState,@FCustomerNo,
@FTelNo,@FTelCount,@FMsg,@FExFl
否则出问题