|
|
Posted by ilo on 09/20/06 15:28
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 (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]
|