June 17, 2009 at 8:47 am
Hello,
Can anyone help me out here?
Description:
I made a trigger for the following:
I have A table F_article and when I update a certain field in here I want to calculate all the sales made between the systemdate -1 month and the system date -3 month and this has to compare the date in table F_docligne
after that I do a sum on the amount to get the result inserted
But It won't work
here's also the trigger:
CREATE TRIGGER [adv_GemVerkoop] ON [dbo].[F_ARTICLE]
FOR INSERT, UPDATE
AS
declare @datemin1 datetime
declare @datemin2 datetime
declare @somMontantHT numeric
declare @do_date smalldatetime
declare @dl_montant numeric
declare @sumdl_montant numeric
declare @documentref varchar
declare @articleRef varchar
declare @cbmarq int
set @datemin1= DATEADD(mm, - 1, GETDATE())
set @datemin2= DATEADD(mm, - 3, GETDATE())
select @sumdl_montant=sum(@dl_montant)
where @datemin1<=@do_date and @datemin2>=@do_date
select @articleRef=AR_ref,
@cbmarq=cbmarq
from inserted
select @documentref=AR_ref
from F_Docligne
select @dl_montant=sum(dl_montantHT)
from F_docligne, F_article
where @datemin1<=F_docligne.do_date and @datemin2>=F_docligne.do_date
select @do_date=do_date
from F_docligne
if update(FA_codefamille)
begin
update F_article
set AR_Prixach=@dl_montant
end
June 17, 2009 at 8:54 am
The first problem with this trigger is that it's assuming there's only one row in the inserted table. If more than one row is affected by an insert/update, the trigger fires once and all of the affected rows are in the inserted table.
Can you explain what this is supposed to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2009 at 8:59 am
Thank's for helping me out
When I update an article it has to go do the table where all the documents are in F_docligne, take all the lines for that article and do the sum of the amount between the dates I declared
Do you have enough information?
Thank a lot
June 17, 2009 at 9:01 am
you also need to define what "won't work" means.
June 17, 2009 at 9:03 am
I get an empty value.
When I do the sum of all document lines It works and it makes a sum of all doc lines, but when I specify the select with the dates and article ref It gives me an empty value
June 17, 2009 at 9:10 am
The other immediate problem is this
if update(FA_codefamille)
begin
update F_article
set AR_Prixach=@dl_montant
end
That will update the entire table with the value in @dl_montant because there's no where clause on the update.
I think you're going to have to give us some example data and show what you want the results to be. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2009 at 9:29 am
It looks like I cannot reach my declared values in order to use them
thx
June 18, 2009 at 1:28 am
Hi,
From the moment I put a where clause in my trigger I get an empty value, so I think I cannot reach my declared values as I need them in order to use them to make a select where.
Thx
June 18, 2009 at 1:36 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2009 at 8:55 am
Can I upload an excel file or send you an email where I putted in al the data and explain what I want this trigger to do.
Thx
Ken
June 19, 2009 at 9:08 am
If you look at that article is covers a way to post sample data. If you can't get that to work, zip and attach an excel spreadsheet with sample data. The expected output and table definitions are still very important.
Post the stuff here, don't mail me.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2009 at 9:22 am
Hello,
So I explained all in an excel sheet in attachment
Could you please help me because I cannot get any result
Thank you very very much
June 19, 2009 at 9:26 am
Table definitions?
Is the first worksheet the sample data? What's in the second worksheet?
Can you perhaps explain, in detail, exactly what the trigger is supposed to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2009 at 9:36 am
Thank's a lot for helping me out
Greetz
Ken
June 19, 2009 at 11:54 am
Hey
Was my last excel well explained about what the trigger has to do?
Greetings
Ken
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply