DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

[ilo]

When I want to delete a data from a table that this tabl has a trigger
and this trigger reached another tables to delete the data in cursor I
have this messeage:

DELETE failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.

My trigger :
CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]
FOR DELETE
AS
BEGIN
DECLARE @rows_affected int, @inc bigint , @dblid bigint ,@DEPOBKINC
bigint
SELECT @rows_affected = @@ROWCOUNT
IF @rows_affected = 0
RETURN -- No rows changed, exit trigger
BEGIN
DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFIER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFIER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC

END
CLOSE Miktar
DEALLOCATE Miktar

END

END

[Erland Sommarskog]

ilo (il********@gmail.com) writes:

When I want to delete a data from a table that this tabl has a trigger
and this trigger reached another tables to delete the data in cursor I
have this messeage:

DELETE failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.

Apparently the target table is part of indexed view. When you work with
an indexed view, the following SET options must be on: ANSI_PADDING,
ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL
and ARITHABORT. Of these the last three depend on run-time values only.
ANSI_PADDNING also depends on how the setting when the columns were
created. And for ANSI_NULLS and QUOTED_IDENTIFIER the setting is saved
when you create with the stored procedure/trigger.

This can lead to problems when people insist on using Enterprise Manager
to edit their SQL objects. Overall EM is a crappy tool for this aim. Use
Query Analyzer which is far superior. Specifically, EM saves objects
with ANSI_NULLS and QUOTED_IDENTIFIER OFF. A second possible culprit is
OSQL which by default runs with QUOTED_IDENTIFIER off.

But before you just save the trigger from Query Analyzer

DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFIER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFIER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC

END
CLOSE Miktar
DEALLOCATE Miktar

This code is completely unacceptable. Replace it with:

DELETE TBLDEPOBKMIKTAR
FROM deleted d
JOIN TBLDEPOBKMIKTAR t ON T.DEPOBKINC = d.DEPOBKINC
AND T.OWNERINC = d.TOPBASICIKISINC
WHERE T.ISLEMID = 2
AND T.BIRIM = 1

The reason your trigger code is unacceptable is that it runs a cursor
for something that can be done in a single statement. If many rows are
deleted at once, there can be several magnitudes in difference in
execution time.

Cursors is something you should use only very exceptionally in SQL
programming, and you should be even more restrictive with it in triggers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ilo]

its working

i changed set options in cursor statement

CREATE TRIGGER [TOPBASICIKISSILME] ON [dbo].[TBLDEPOBKTOPBASICIKIS]
FOR DELETE
AS
BEGIN
DECLARE @rows_affected int, @inc bigint , @dblid bigint ,@depobkinc
bigint
SELECT @rows_affected = @@ROWCOUNT
IF @rows_affected = 0
RETURN -- No rows changed, exit trigger
BEGIN
DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@depobkinc
WHILE @@fetch_status = 0
BEGIN
-- added new ----
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
--- added new finish ------

DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@depobkinc and
OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
PRINT @depobkinc
PRINT @inc
FETCH NEXT FROM Miktar INTO @dblid,@inc,@depobkinc

END
CLOSE Miktar
DEALLOCATE Miktar

END

END

Erland Sommarskog yazdi:

ilo (il********@gmail.com) writes:

When I want to delete a data from a table that this tabl has a trigger
and this trigger reached another tables to delete the data in cursor I
have this messeage:

DELETE failed because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.

Apparently the target table is part of indexed view. When you work with
an indexed view, the following SET options must be on: ANSI_PADDING,
ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL
and ARITHABORT. Of these the last three depend on run-time values only.
ANSI_PADDNING also depends on how the setting when the columns were
created. And for ANSI_NULLS and QUOTED_IDENTIFIER the setting is saved
when you create with the stored procedure/trigger.

This can lead to problems when people insist on using Enterprise Manager
to edit their SQL objects. Overall EM is a crappy tool for this aim. Use
Query Analyzer which is far superior. Specifically, EM saves objects
with ANSI_NULLS and QUOTED_IDENTIFIER OFF. A second possible culprit is
OSQL which by default runs with QUOTED_IDENTIFIER off.

But before you just save the trigger from Query Analyzer

DECLARE Miktar CURSOR FOR
SELECT deleted.DBLID,deleted.TOPBASICIKISINC , deleted.DEPOBKINC
FROM deleted
OPEN Miktar
FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC
WHILE @@fetch_status = 0
BEGIN
SET QUOTED_IDENTIFIER ON
DELETE FROM TBLDEPOBKMIKTAR WHERE DEPOBKINC=@DEPOBKINC
AND OWNERINC = @inc AND ISLEMID=2 AND HAREKETID=19 AND BIRIM=1
SET QUOTED_IDENTIFIER OFF
PRINT @DEPOBKINC

FETCH NEXT FROM Miktar INTO @dblid,@inc,@DEPOBKINC

END
CLOSE Miktar
DEALLOCATE Miktar

This code is completely unacceptable. Replace it with:

DELETE TBLDEPOBKMIKTAR
FROM deleted d
JOIN TBLDEPOBKMIKTAR t ON T.DEPOBKINC = d.DEPOBKINC
AND T.OWNERINC = d.TOPBASICIKISINC
WHERE T.ISLEMID = 2
AND T.BIRIM = 1

The reason your trigger code is unacceptable is that it runs a cursor
for something that can be done in a single statement. If many rows are
deleted at once, there can be several magnitudes in difference in
execution time.

Cursors is something you should use only very exceptionally in SQL
programming, and you should be even more restrictive with it in triggers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

ilo (il********@gmail.com) writes:

its working

i changed set options in cursor statement

Maybe it's "working" but there are serious performance problems with the
code. Those SET statements causes the trigger to be recompile twice
during execution which is completely unnecessary. And the cursor can be
a complete disaster for performance.

I don't know why you are wrting triggers in the first place, but I someone
- a client or an employer - pays you for it. Whatever, it is complete
irresponsible to leave code like this, not the least when you have been
told what the appropriate procedures are.

Please remove that cursor and take out those SET statements, and ionsted
save the trigger correctly.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Apparently the target table is part of indexed view.

or it has an index on a computed column - same restrictions.

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

[ilo]

i tried but its giving same error message

Erland Sommarskog yazdi:

ilo (il********@gmail.com) writes:

its working

i changed set options in cursor statement

Maybe it's "working" but there are serious performance problems with the
code. Those SET statements causes the trigger to be recompile twice
during execution which is completely unnecessary. And the cursor can be
a complete disaster for performance.

I don't know why you are wrting triggers in the first place, but I someone
- a client or an employer - pays you for it. Whatever, it is complete
irresponsible to leave code like this, not the least when you have been
told what the appropriate procedures are.

Please remove that cursor and take out those SET statements, and ionsted
save the trigger correctly.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ilo]

ok i did it your way

first i dropped trigger and

i set on queryidentifier and ansi null on and then i created trigger
your way then it works

thanks for you advice

Erland Sommarskog yazdi:

ilo (il********@gmail.com) writes:

its working

i changed set options in cursor statement

Maybe it's "working" but there are serious performance problems with the
code. Those SET statements causes the trigger to be recompile twice
during execution which is completely unnecessary. And the cursor can be
a complete disaster for performance.

I don't know why you are wrting triggers in the first place, but I someone
- a client or an employer - pays you for it. Whatever, it is complete
irresponsible to leave code like this, not the least when you have been
told what the appropriate procedures are.

Please remove that cursor and take out those SET statements, and ionsted
save the trigger correctly.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx