Convert trigger to a stored procedure

Question

Convert trigger to a stored procedure

adriaensken

Say Hey Kid

Points: 684
More actions
June 21, 2009 at 11:57 am

#75486

Hello,
I will explain what I mean here to do:
I have a certain trigger who fires when an article is updated for ex.
I open an article tab and make a change, then the trigger will be fired
what does the trigger:
It takes all the sales document lines together and make a sum of the amount.
Now, when I do this when I consult the article It works all fine and perfect, but I like to change it to a stored procedure so I can shedule it and do the update for all articles at once, but het takes for all the articles the same value when I do that.
How can I fix this
Many thanks
my trigger:
CREATE TRIGGER [adv_GemVerkoop] ON [dbo].[F_ARTICLE]
FOR INSERT, UPDATE
AS
declare @datemin1 smalldatetime
declare @datemin2 smalldatetime
declare @do_date smalldatetime
declare @sumdl_montant numeric
declare @cbmarq int
set @datemin1= DATEADD(mm, - 1, GETDATE())
set @datemin2= DATEADD(mm, - 3, GETDATE())
select @cbmarq=cbmarq
from inserted
select @sumdl_montant=(sum(dl_montantHT)/12)
from F_docligne inner join inserted on F_docligne.AR_REF=inserted.ar_ref
where do_domaine=0 AND Do_date <=@datemin1 and do_date >=@datemin2
and do_type=7
begin
update F_article
set AR_prixach=@sumdl_montant
where @cbmarq=cbmarq
end

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 1

There are two things with this/

1) the trigger supposes it will be fired after completen an update for single @cbmarq. If you update a table out of that "regular" oltp usage, you may end up with pretty messed up stuff.

2) If converting this to a sproc, you will have to add a group level for cbmarq !

btw: This trigger and/or sproc action may get deadlock conflichts, because they will handle more than the in transaction updated rows.

You can directly update your target table this way:

(You may need to alter the use aliasses for the column refs, because I assumed these columns all come from the used table)

update A

set AR_prixach=Sum_dl_montantHT_12

from F_articlewhere A

inner join (

select i.cbmarq, sum(dl_montantHT)/12) as Sum_dl_montantHT_12

from F_docligne DL

inner join inserted i

on DL.AR_REF = i.ar_ref

where i.do_domaine=0

AND i.Do_date =@datemin2

and i.do_type=7

group by i.cbmarq

) S

on S.cbmarq=A.cbmarq

If sizes run up, you will have to provide the needed indexes to support this operation (either way)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

adriaensken Say Hey Kid Points: 684 More actions · Answer 2

Sorry for all thes questions but I am a really newbie to sql language and I do not see very well how it wil work now,

I pasted the code but it does not work yet, can you please tell me what you mean with the A in the code?

Thx

Ken

adriaensken Say Hey Kid Points: 684 More actions · Answer 3

Still get error near the keyword from and that I cannot use inserted in stored proc?

thx

adriaensken Say Hey Kid Points: 684 More actions · Answer 4

Or is it possible to fire the trigger when openenig an article is there such a function, just when I consult It?

thx

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 5

- The "inserted" and "deleted" (table) objects are only available in the trigger itself.

- The A in the code is the table alias for table F_articlewhere, meaning in that query this tables columns will be addresses using the prefix "A."

- Check Books Online topic "Create trigger" and you'll find a trigger is created for a certain action (Insert / Update / Delete)