Cursor in Trigger

Hi All, I have a problem when I try to use a Cursor for update inside a
trigger, the problem is that when I try to update another table using the
values for temporary tables "inserted" or "deleted" appers this error: "For
update cannot be specified on a read only cursor. Error 16957. Severity 16"
I hope some one can help meNote:
This problem does not occurs in SQL 7, just in 2000
"Ton" wrote:
> Hi All, I have a problem when I try to use a Cursor for update inside a
> trigger, the problem is that when I try to update another table using the
> values for temporary tables "inserted" or "deleted" appers this error: "For
> update cannot be specified on a read only cursor. Error 16957. Severity 16"
> I hope some one can help me|||Ton wrote:
> Hi All, I have a problem when I try to use a Cursor for update inside
> a trigger, the problem is that when I try to update another table
> using the values for temporary tables "inserted" or "deleted" appers
> this error: "For update cannot be specified on a read only cursor.
> Error 16957. Severity 16"
> I hope some one can help me
Post your code please.
--
David Gugick
Imceda Software
www.imceda.com|||inserted and deleted logical tables r readonly.
U cant use them in an update cursor
"Ton" <Ton@.discussions.microsoft.com> wrote in message
news:69354871-442B-48DF-8C98-D544C3FE11AB@.microsoft.com...
> Hi All, I have a problem when I try to use a Cursor for update inside a
> trigger, the problem is that when I try to update another table using the
> values for temporary tables "inserted" or "deleted" appers this error:
"For
> update cannot be specified on a read only cursor. Error 16957. Severity
16"
> I hope some one can help me|||USE FICS
go
---
-- Type: Trigger
-- Name: trIFisTrackerItem011
---
CREATE TRIGGER trIFisTrackerItem011
ON TRACKERITEM011
WITH ENCRYPTION
FOR INSERT AS
/*----*/
/* Proposito.: Trigger de INSERT */
/*----*/
/* Historial.: */
/* */
/* */
/*----*/
/*
** General Variable
*/
DECLARE @.numrows int,
@.errno int,
@.errmsg varchar(255),
@.INSERT T_udtActionType,
@.UPDATE T_udtActionType,
@.DELETE T_udtActionType,
@.SELECT T_udtActionType,
@.REPORT T_udtActionType
/*
** Process Variable
*/
DECLARE @.SerialLog T_udtIDStrAlt,
@.ProductLog T_udtIDStrAlt,
@.RegionLog T_udtIDStrAlt,
@.LocationLog T_udtIDStrAlt,
@.Reference1 T_udtIDStrAlt,
@.TimeStampLog T_udtDateTime
/*
** Affected rows
*/
SELECT @.numrows = @.@.ROWCOUNT
/*
** If have affected rows
*/
IF @.numrows = 0
RETURN
SET NOCOUNT ON
/*
** Transaction type
*/
SELECT @.INSERT = 1,
@.UPDATE = 2,
@.DELETE = 3,
@.SELECT = 4,
@.REPORT = 5
/*
**=========================================** BEGIN PROCESS
**=========================================*/
/*
** Cursor declare for inserted rows
*/
DECLARE TrackerItem011_Cursor SCROLL CURSOR
FOR SELECT TKI.SERIALLOG_VAL0,
TKI.PRODUCTLOG_VAL0,
TKI.REGIONLOG_VAL0,
TKI.LOCATIONLOG_VAL0,
TKI.REFERENCELOG_VAL0,
TKI.timestamp
FROM TRACKERITEM011 TKI
WHERE ISNULL(TKI.InTracker, 0) = 0
AND TKI.SERIALLOG_VAL0 IS NOT NULL
AND EXISTS(SELECT 1
FROM Inserted INS
WHERE INS.timeStamp = TKI.timeStamp
AND INS.project = TKI.project
)
FOR UPDATE OF TKI.InTracker
/*
** Open cursor
*/
OPEN TrackerItem011_Cursor
/*
** First record
*/
FETCH NEXT FROM TrackerItem011_Cursor
INTO @.SerialLog,
@.ProductLog,
@.RegionLog,
@.LocationLog,
@.Reference1,
@.TimeStampLog
/*
** Loop over cursor
*/
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*
** Generate Tracker Item
*/
EXECUTE spFisXSetTrackerItem @.SerialLog, @.ProductLog, @.RegionLog,
@.LocationLog, @.TimeStampLog, @.Reference1, NULL, NULL, 0
/*::::::::::::::::::::::::::::::::::::::::::::*/
/* Check for error */
/*::::::::::::::::::::::::::::::::::::::::::::*/
IF @.@.ERROR <> 0
BEGIN
SELECT @.errno = 80001,
@.errmsg = "Transaction Fault into 'TRACKERITEM011' table
while update tracker information. Serial='" + ISNULL(@.SerialLog, 'NULL') + "'
Reference='" + ISNULL(@.ProductLog, 'NULL') + "' Region='" +
ISNULL(@.RegionLog, 'NULL') + "'."
GOTO errHandler
END
/*
** Update this record
*/
UPDATE TRACKERITEM011
SET InTracker = 1
WHERE CURRENT OF TrackerItem011_Cursor
/*
** Next record
*/
FETCH NEXT FROM TrackerItem011_Cursor
INTO @.SerialLog,
@.ProductLog,
@.RegionLog,
@.LocationLog,
@.Reference1,
@.TimeStampLog
END
/*
** Destroy cursor
*/
CLOSE TrackerItem011_Cursor
DEALLOCATE TrackerItem011_Cursor
/*::::::::::::::::::::::::::::::::::::::::::::*/
/* Check for error */
/*::::::::::::::::::::::::::::::::::::::::::::*/
IF @.@.ERROR <> 0
BEGIN
SELECT @.errno = 80001,
@.errmsg = "Transaction Fault into 'TRACKERITEM011' table while
update tracker information. Serial='" + ISNULL(@.SerialLog, 'NULL') + "'
Reference='" + ISNULL(@.ProductLog, 'NULL') + "' Region='" +
ISNULL(@.RegionLog, 'NULL') + "' Location='" + ISNULL(@.LocationLog, 'NULL') +
"'."
GOTO errHandler
END
/*
** Exit point
*/
ExitPoint:
SET NOCOUNT OFF
RETURN
/*
** Handle Error
*/
errHandler:
EXEC master.dbo.xp_logevent @.errno, @.errmsg, informational
RAISERROR @.errno @.errmsg
ROLLBACK TRANSACTION
GOTO ExitPoint
"David Gugick" wrote:
> Ton wrote:
> > Hi All, I have a problem when I try to use a Cursor for update inside
> > a trigger, the problem is that when I try to update another table
> > using the values for temporary tables "inserted" or "deleted" appers
> > this error: "For update cannot be specified on a read only cursor.
> > Error 16957. Severity 16"
> >
> > I hope some one can help me
> Post your code please.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Review the infomation in BOL under the topic "Implicit cursor conversions".
According to this, your cursor is implicitly converted to a static cursor
(which is not updateable) due to the query used.
"Ton" <Ton@.discussions.microsoft.com> wrote in message
news:178BF62D-9B73-453E-838F-07E72A012A18@.microsoft.com...
> USE FICS
> go
> ---
> -- Type: Trigger
> -- Name: trIFisTrackerItem011
> ---
>
> CREATE TRIGGER trIFisTrackerItem011
> ON TRACKERITEM011
> WITH ENCRYPTION
> FOR INSERT AS
>
/*----
*/
> /* Proposito.: Trigger de INSERT
*/
>
/*----
*/
> /* Historial.:
*/
> /*
*/
> /*
*/
>
/*----
*/
> /*
> ** General Variable
> */
> DECLARE @.numrows int,
> @.errno int,
> @.errmsg varchar(255),
> @.INSERT T_udtActionType,
> @.UPDATE T_udtActionType,
> @.DELETE T_udtActionType,
> @.SELECT T_udtActionType,
> @.REPORT T_udtActionType
> /*
> ** Process Variable
> */
> DECLARE @.SerialLog T_udtIDStrAlt,
> @.ProductLog T_udtIDStrAlt,
> @.RegionLog T_udtIDStrAlt,
> @.LocationLog T_udtIDStrAlt,
> @.Reference1 T_udtIDStrAlt,
> @.TimeStampLog T_udtDateTime
> /*
> ** Affected rows
> */
> SELECT @.numrows = @.@.ROWCOUNT
> /*
> ** If have affected rows
> */
> IF @.numrows = 0
> RETURN
> SET NOCOUNT ON
> /*
> ** Transaction type
> */
> SELECT @.INSERT = 1,
> @.UPDATE = 2,
> @.DELETE = 3,
> @.SELECT = 4,
> @.REPORT = 5
> /*
> **=========================================> ** BEGIN PROCESS
> **=========================================> */
> /*
> ** Cursor declare for inserted rows
> */
> DECLARE TrackerItem011_Cursor SCROLL CURSOR
> FOR SELECT TKI.SERIALLOG_VAL0,
> TKI.PRODUCTLOG_VAL0,
> TKI.REGIONLOG_VAL0,
> TKI.LOCATIONLOG_VAL0,
> TKI.REFERENCELOG_VAL0,
> TKI.timestamp
> FROM TRACKERITEM011 TKI
> WHERE ISNULL(TKI.InTracker, 0) = 0
> AND TKI.SERIALLOG_VAL0 IS NOT NULL
> AND EXISTS(SELECT 1
> FROM Inserted INS
> WHERE INS.timeStamp = TKI.timeStamp
> AND INS.project = TKI.project
> )
> FOR UPDATE OF TKI.InTracker
> /*
> ** Open cursor
> */
> OPEN TrackerItem011_Cursor
> /*
> ** First record
> */
> FETCH NEXT FROM TrackerItem011_Cursor
> INTO @.SerialLog,
> @.ProductLog,
> @.RegionLog,
> @.LocationLog,
> @.Reference1,
> @.TimeStampLog
> /*
> ** Loop over cursor
> */
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /*
> ** Generate Tracker Item
> */
> EXECUTE spFisXSetTrackerItem @.SerialLog, @.ProductLog, @.RegionLog,
> @.LocationLog, @.TimeStampLog, @.Reference1, NULL, NULL, 0
> /*::::::::::::::::::::::::::::::::::::::::::::*/
> /* Check for error */
> /*::::::::::::::::::::::::::::::::::::::::::::*/
> IF @.@.ERROR <> 0
> BEGIN
> SELECT @.errno = 80001,
> @.errmsg = "Transaction Fault into 'TRACKERITEM011' table
> while update tracker information. Serial='" + ISNULL(@.SerialLog, 'NULL') +
"'
> Reference='" + ISNULL(@.ProductLog, 'NULL') + "' Region='" +
> ISNULL(@.RegionLog, 'NULL') + "'."
> GOTO errHandler
> END
> /*
> ** Update this record
> */
> UPDATE TRACKERITEM011
> SET InTracker = 1
> WHERE CURRENT OF TrackerItem011_Cursor
> /*
> ** Next record
> */
> FETCH NEXT FROM TrackerItem011_Cursor
> INTO @.SerialLog,
> @.ProductLog,
> @.RegionLog,
> @.LocationLog,
> @.Reference1,
> @.TimeStampLog
> END
> /*
> ** Destroy cursor
> */
> CLOSE TrackerItem011_Cursor
> DEALLOCATE TrackerItem011_Cursor
>
> /*::::::::::::::::::::::::::::::::::::::::::::*/
> /* Check for error */
> /*::::::::::::::::::::::::::::::::::::::::::::*/
> IF @.@.ERROR <> 0
> BEGIN
> SELECT @.errno = 80001,
> @.errmsg = "Transaction Fault into 'TRACKERITEM011' table
while
> update tracker information. Serial='" + ISNULL(@.SerialLog, 'NULL') + "'
> Reference='" + ISNULL(@.ProductLog, 'NULL') + "' Region='" +
> ISNULL(@.RegionLog, 'NULL') + "' Location='" + ISNULL(@.LocationLog, 'NULL')
+
> "'."
> GOTO errHandler
> END
> /*
> ** Exit point
> */
> ExitPoint:
> SET NOCOUNT OFF
> RETURN
> /*
> ** Handle Error
> */
> errHandler:
> EXEC master.dbo.xp_logevent @.errno, @.errmsg, informational
> RAISERROR @.errno @.errmsg
> ROLLBACK TRANSACTION
> GOTO ExitPoint
>
> "David Gugick" wrote:
> > Ton wrote:
> > > Hi All, I have a problem when I try to use a Cursor for update inside
> > > a trigger, the problem is that when I try to update another table
> > > using the values for temporary tables "inserted" or "deleted" appers
> > > this error: "For update cannot be specified on a read only cursor.
> > > Error 16957. Severity 16"
> > >
> > > I hope some one can help me
> >
> > Post your code please.
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >