Cursor in Triggers ? ... What's best practice

[xoro]

Hi,

I'm getting a new problem with cursors in triggers.
I would like to replace all my cursor fetching in all triggers but i did'nt know which is the best pratice.

Here is a sample of my trigger code :

ALTER TRIGGER [dbo].[tia_tarif_vente] ON [dbo].[tarif_vente]
FOR insert
AS
declare @tarif_vente_numart integer
declare @tarif_vente_codtar varchar(10)
declare @tarif_vente_datdebval datetime
declare @curs_new_datfinval datetime
declare @curs_new_prixventedefinitif numeric(15,3)


DECLARE curs_tia_tarif_vente CURSOR LOCAL
FOR
select i.numart,i.codtar,i.datdebval,i.datfinval,i.prixventedefinitif from inserted i

OPEN curs_tia_tarif_vente

FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
WHILE @@Fetch_Status = 0
BEGIN

	if @tarif_vente_codtar = 'G'
		exec dbo.tg_tarif_vente_tib @tarif_vente_codtar,@tarif_vente_numart,@curs_new_datfinval,@curs_new_prixventedefinitif
	
	FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif

END

CLOSE curs_tia_tarif_vente

DEALLOCATE curs_tia_tarif_vente


return

Is there a way to don't use cursors ?

Thanks in advance.

[ck9663]

This would depend on what's going on inside "dbo.tg_tarif_v ente_tib"

-- CK

[guptapawan29]

Hi,

I'm getting a new problem with cursors in triggers.
I would like to replace all my cursor fetching in all triggers but i did'nt know which is the best pratice.

Here is a sample of my trigger code :

ALTER TRIGGER [dbo].[tia_tarif_vente] ON [dbo].[tarif_vente]
FOR insert
AS
declare @tarif_vente_numart integer
declare @tarif_vente_codtar varchar(10)
declare @tarif_vente_datdebval datetime
declare @curs_new_datfinval datetime
declare @curs_new_prixventedefinitif numeric(15,3)


DECLARE curs_tia_tarif_vente CURSOR LOCAL
FOR
select i.numart,i.codtar,i.datdebval,i.datfinval,i.prixventedefinitif from inserted i

OPEN curs_tia_tarif_vente

FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
WHILE @@Fetch_Status = 0
BEGIN

	if @tarif_vente_codtar = 'G'
		exec dbo.tg_tarif_vente_tib @tarif_vente_codtar,@tarif_vente_numart,@curs_new_datfinval,@curs_new_prixventedefinitif
	
	FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif

END

CLOSE curs_tia_tarif_vente

DEALLOCATE curs_tia_tarif_vente


return

Is there a way to don't use cursors ?

Thanks in advance.

hi........

As ...your requirement looks ..u are fetching data from magic table and by using cursor ...you are using those values.
I think you do not need to use cursor if you directly strore those values into variables and use them accoring...your code might look like this..

select @tarif_vente_nu mart=i.numart,
@tarif_vente_co dtar=i.codtar,
@tarif_vente_da tdebval=i.datde bval,
@curs_new_datfi nval=i.datfinva l,
@curs_new_prixv entedefinitif =i.prixventedef initif
from inserted i


if @tarif_vente_co dtar = 'G'
exec dbo.tg_tarif_ve nte_tib @tarif_vente_co dtar,@tarif_ven te_numart,@curs _new_datfinval, @curs_new_prixv entedefinitif

I hope above indication may help you
--Pawan Gupta