SQL Server Performance Forum – Threads Archive

Trigger Cursor Error

Can anyone help? If you have e.g.
insert into Product (…) values (…)
insert into Product (…) values (…)
GO
insert into Product (…) values (…)
GO …with the following trigger, ALTER TRIGGER Product_trigger_I
ON dbo.Product
AFTER INSERT
AS
SELECT @@TRANCOUNT DECLARE @ProductCode INT
DECLARE @ProductID INT
DECLARE Inserted_Cursor CURSOR STATIC FOR
(
SELECT ProductID, HOProductCode
FROM Inserted
WHERE HOProductCode IS NULL
)
OPEN Inserted_Cursor FETCH NEXT FROM Inserted_Cursor INTO @ProductID, @ProductCode
WHILE @@FETCH_STATUS = 0
BEGIN SELECT @ProductCode = Min(ProductCode)
FROM zs_AvailableProductCodes (TABLOCK) UPDATE p
SET p.HOProductCode = @ProductCode
FROM dbo.Product p
WHERE p.ProductID = @ProductID IF @@ERROR > 0 OR @@ROWCOUNT = 0 — CHECK TO SEE IF UPDATE SUCCEEDED —
RAISERROR (50001, 16, 1, ‘Product’, @ProductCode) — Excepts nulls — Now remove used item from list of Available Product Codes —
DELETE FROM
zs_AvailableProductCodes
WHERE ProductCode = @ProductCode IF @@ERROR > 0 OR @@ROWCOUNT = 0 — CHECK TO SEE IF DELETE SUCCEEDED —
RAISERROR (50002, 16, 1, @ProductCode) — Excepts nulls FETCH NEXT FROM Inserted_Cursor INTO @ProductID, @ProductCode
END CLOSE Inserted_Cursor
DEALLOCATE Inserted_Cursor
GO …an error will be raised saying that Cursor "Inserted_Cursor" is open. This appears to be a result of the trigger not having finished executing from a prior insert request. The @@TRANCOUNT is greater than one, but how can I get SQL Server to re-execute the trigger when the cursor is closed. Regards,
Rik

You could perhaps try: 1. run process with the trigger as above but run Profiler to get all of the individual statements being fired (sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />tmtCompleted) 2. comment out the raiserrors to see if they cause the problem There is no update trigger that could be causing this? i.e. the name Inserted_Cursor has not been used by other triggers? Cheers Twan

I think you should deallocate the cursor before raiseerror. IF @@ERROR > 0 OR @@ROWCOUNT = 0 — CHECK TO SEE IF UPDATE SUCCEEDED —
BEGIN
DEALLOCATE Inserted_Cursor
RAISERROR (50001, 16, 1, ‘Product’, @ProductCode)
END
BTW, you are not checking @ProductCode value after the SELECT and before the UPDATE. So there could be a situation in which no code is available, but you would still be going through the update. (Maybe this is how you wanted it)

Bambola, the raiserror won’t automatically abort the query will it? Otherwise code such as raiserror …
rollback transaction would result in a transaction being left open…? Cheers
Twan

Hi All, Thanx for the replies.
I have tried many of your suggestions, but found that it was best to avoid using a cursor. If I was to use a cursor, I could have perhaps tried using a transaction with SET CURSOR_CLOSE_ON_COMMIT ON being called before anything else. The task required the following:
1) Information gets pushed to sites (running sql 6.5)
"NewCode" is defined as a smallint at the sites (and when it is eventually upgraded to sql 2k, will be converted to an integer datatype).
2) central database needs to generate a "NewCode" value and does this by taking values from a table that hold all unused values across all sites, between 1 and 32767.
3) central database "NewCode" value must be unique across 3 tables. Can’t add unique constraint as the field needs to be nullable as it is generated as part of the after insert trigger. Instead of using cursors, I found a way of getting around the job done by implementing something like the following. In answer to a your question about response within triggers to a RaiseError statement:
The RaiseError statement will not result in the trigger exiting after called. set nocount on
GO
print ‘Testing Transaction locking within a trigger.’
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableNewCode1]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[TableNewCode1]
go
create table TableNewCode1 (PKID INT IDENTITY(1,1) NOT NULL, NewCode INT NULL)
insert into TableNewCode1 (NewCode) values (1 )
insert into TableNewCode1 (NewCode) values (2 )
insert into TableNewCode1 (NewCode) values (3 )
insert into TableNewCode1 (NewCode) values (4 )
insert into TableNewCode1 (NewCode) values (5 )
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TableNewCode2]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[TableNewCode2]
go
create table TableNewCode2 (PKID INT IDENTITY(1,1) NOT NULL, NewCode INT NULL)
insert into TableNewCode2 (NewCode) values (6 )
insert into TableNewCode2 (NewCode) values (7 )
insert into TableNewCode2 (NewCode) values (8 )
insert into TableNewCode2 (NewCode) values (9 )
insert into TableNewCode2 (NewCode) values (10)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestLocking]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[TestLocking]
go
create table TestLocking (PKID INT IDENTITY(1,1) NOT NULL, FLD INT NOT NULL, NewCode INT NULL)
insert into TestLocking (FLD, NewCode) values (1, 11)
insert into TestLocking (FLD, NewCode) values (1, 12)
insert into TestLocking (FLD, NewCode) values (1, 13)
insert into TestLocking (FLD, NewCode) values (1, 14)
insert into TestLocking (FLD, NewCode) values (1, 15)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_NewCodes]’) and OBJECTPROPERTY(id, N’IsView’) = 1)
drop view [dbo].[vw_NewCodes]
GO
create view vw_NewCodes as
— need to be able to see duplicates —
select NewCode
from TableNewCode1
union all
select NewCode
from TableNewCode2
union all
select NewCode
from TestLocking
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LookupCodes]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table LookupCodes
go
— create and populate table to hold all "available" (non-used) numbers —
create table LookupCodes (NewCode INT NOT NULL)
DECLARE @available_numbers INT
SELECT @available_numbers = 1 WHILE @available_numbers <= 100
BEGIN
INSERT INTO LookupCodes VALUES (@available_numbers)
SELECT @available_numbers = @available_numbers + 1
END — remove used numbers from table LookupCodes —
delete
from LookupCodes
where NewCode in
(
select NewCode
from vw_NewCodes
)
GO CREATE TRIGGER TestLocking_trigger_I
ON dbo.TestLocking
AFTER INSERT
AS
———————————————————————————–
— Created By: Richard Beacroft Date: 29/12/2003 —
———————————————————————————–
SET NOCOUNT ON
— constraints cannot exist on the field NewCode as After Trigger required —
— NewCode values must be unique across the following tables: Product, TestLocking, TestLocking
DECLARE @RowsInserted INT CREATE TABLE #Inserted (PK_IDENT INT IDENTITY(1,1) NOT NULL, PKID INT NOT NULL)
CREATE TABLE #LookupCodes (PK_IDENT INT IDENTITY(1,1) NOT NULL, NewCode INT NOT NULL) IF EXISTS (SELECT 1 FROM INSERTED WHERE NOT NewCode IS NULL) — quick check for relevance to insert carried out
BEGIN
— Are there specified NewCodes already present in the database? —
IF EXISTS (SELECT 1
FROM vw_NewCodes vwc
JOIN Inserted i ON (i.NewCode = vwc.NewCode)
GROUP BY i.NewCode
HAVING COUNT(i.NewCode) > 1 — find records where > 2 NewCodes exist for each inserted record —
)
BEGIN
— Greater than 2 because this is an After Insert trigger, record present in table Inserted and Product table —
— eventually log information into database error table —
RAISERROR (50000, 16, 1) — currently undefined – create appropriate error message —
— Remove duplicated items from the table TestLocking —
DELETE tc
FROM TestLocking tc
JOINInserted i ON (i.PKID = tc.PKID) — must join on PK
/*
— Remove all records attempted to insert: Keep to transaction ATOMIC rules —
JOIN vw_NewCodes vwc ON (i.NewCode = vpc.NewCode )
*/
RETURN
END
ELSE
BEGIN
— remove from the list of available codes those specified but not yet present —
DELETE lc
FROM LookupCodes lc
JOIN Inserted i ON (i.NewCode = lc.NewCode)
END IF NOT EXISTS (SELECT 1 FROM Inserted WHERE NewCode IS NULL) — no NewCodes need to be determined —
RETURN — nothing to update —
END BEGIN TRANSACTION
INSERT INTO #Inserted (PKID)
SELECT PKID
FROM Inserted
WHERE NewCode IS NULL
SELECT @RowsInserted = @@ROWCOUNT — populate #LookupCodes with the correct number of available NewCodes to use —
SET ROWCOUNT @RowsInserted
INSERT INTO #LookupCodes (NewCode)
SELECT NewCode
FROM LookupCodes
SET ROWCOUNT 0 WAITFOR DELAY ’00:00:05.000′ — wait 5 seconds (testing locking) —
print ‘Which NewCode has been retrieved’
/*
If 2 connections are adding 2 records at exactly the same time, does the second wait
for the first one to complete before retrieving the appropriate NewCode……YES, IT DOES!
*/
select * from #LookupCodes — update TestLocking with the new NewCodes —
UPDATE tc
SET tc.NewCode = c.NewCode
FROM dbo.TestLocking tc
JOIN #Inserted i ON (i.PKID = tc.PKID )
JOIN #LookupCodes c ON (c.PK_IDENT = i.PK_IDENT) — the reason for the temp tables — IF @@ERROR > 0 OR @@ROWCOUNT = 0 — CHECK TO SEE IF UPDATE SUCCEEDED —
BEGIN
— eventually log information into database error table —
RAISERROR (50000, 16, 1) — currently undefined – create appropriate error message —
ROLLBACK TRANSACTION
RETURN
END — Now remove used items from list of available NewCode’s —
SET ROWCOUNT @RowsInserted
DELETE FROM
LookupCodes — Will remove first @RowsInserted numbers from the table —
IF @@ERROR > 0 OR @@ROWCOUNT = 0 — CHECK TO SEE IF DELETE SUCCEEDED —
BEGIN — cannot envisage a situation where this problem could arise —
— eventually log information into database error table —
RAISERROR (50000, 16, 1) — currently undefined – create appropriate error message —
SET ROWCOUNT 0
ROLLBACK TRANSACTION
RETURN
END
SET ROWCOUNT 0 COMMIT TRANSACTION SET NOCOUNT OFF
GO
— A way of testing Transaction locking within a trigger —
print ‘1) Try adding a record to TestLocking without specifying a NewCode’
select * from TestLocking
insert into TestLocking (FLD, NewCode) values (2, null) — NewCode not-specified! — should get next available and apply it —
select * from TestLocking
GO
print ‘2) Try adding a record to TestLocking without specifying a NewCode’
select * from TestLocking
insert into TestLocking (FLD, NewCode) values (2, null) — NewCode not-specified! — should get next available and apply it —
select * from TestLocking
GO
SET NOCOUNT OFF
GO Regards,
Rik

]]>