|
|
Posted by Erland Sommarskog on 09/20/06 10:48
ilo (ilyas.isik@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|