By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

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

P: 1
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 :

Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [dbo].[tia_tarif_vente] ON [dbo].[tarif_vente]
  2. FOR insert
  3. AS
  4. declare @tarif_vente_numart integer
  5. declare @tarif_vente_codtar varchar(10)
  6. declare @tarif_vente_datdebval datetime
  7. declare @curs_new_datfinval datetime
  8. declare @curs_new_prixventedefinitif numeric(15,3)
  9.  
  10.  
  11. DECLARE curs_tia_tarif_vente CURSOR LOCAL
  12. FOR
  13. select i.numart,i.codtar,i.datdebval,i.datfinval,i.prixventedefinitif from inserted i
  14.  
  15. OPEN curs_tia_tarif_vente
  16.  
  17. FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
  18. WHILE @@Fetch_Status = 0
  19. BEGIN
  20.  
  21.     if @tarif_vente_codtar = 'G'
  22.         exec dbo.tg_tarif_vente_tib @tarif_vente_codtar,@tarif_vente_numart,@curs_new_datfinval,@curs_new_prixventedefinitif
  23.  
  24.     FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
  25.  
  26. END
  27.  
  28. CLOSE curs_tia_tarif_vente
  29.  
  30. DEALLOCATE curs_tia_tarif_vente
  31.  
  32.  
  33. return
  34.  
Is there a way to don't use cursors ?

Thanks in advance.
Apr 9 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
This would depend on what's going on inside "dbo.tg_tarif_vente_tib"

-- CK
Apr 9 '08 #2

P: 4
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 :

Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [dbo].[tia_tarif_vente] ON [dbo].[tarif_vente]
  2. FOR insert
  3. AS
  4. declare @tarif_vente_numart integer
  5. declare @tarif_vente_codtar varchar(10)
  6. declare @tarif_vente_datdebval datetime
  7. declare @curs_new_datfinval datetime
  8. declare @curs_new_prixventedefinitif numeric(15,3)
  9.  
  10.  
  11. DECLARE curs_tia_tarif_vente CURSOR LOCAL
  12. FOR
  13. select i.numart,i.codtar,i.datdebval,i.datfinval,i.prixventedefinitif from inserted i
  14.  
  15. OPEN curs_tia_tarif_vente
  16.  
  17. FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
  18. WHILE @@Fetch_Status = 0
  19. BEGIN
  20.  
  21.     if @tarif_vente_codtar = 'G'
  22.         exec dbo.tg_tarif_vente_tib @tarif_vente_codtar,@tarif_vente_numart,@curs_new_datfinval,@curs_new_prixventedefinitif
  23.  
  24.     FETCH curs_tia_tarif_vente INTO @tarif_vente_numart,@tarif_vente_codtar,@tarif_vente_datdebval,@curs_new_datfinval,@curs_new_prixventedefinitif
  25.  
  26. END
  27.  
  28. CLOSE curs_tia_tarif_vente
  29.  
  30. DEALLOCATE curs_tia_tarif_vente
  31.  
  32.  
  33. return
  34.  
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_numart=i.numart,
@tarif_vente_codtar=i.codtar,
@tarif_vente_datdebval=i.datdebval,
@curs_new_datfinval=i.datfinval,
@curs_new_prixventedefinitif =i.prixventedefinitif
from inserted i


if @tarif_vente_codtar = 'G'
exec dbo.tg_tarif_vente_tib @tarif_vente_codtar,@tarif_vente_numart,@curs_new_ datfinval,@curs_new_prixventedefinitif

I hope above indication may help you
--Pawan Gupta
Apr 11 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.