CODEWITHSUNDEEP
sql-server-2008triggers
Surbhi
Surbhi

Reputation: 111

sql trigger to do sum,average

I am writing a trigger for very first time can anyone help me to resolve this issue

What actually i want to accomplish is, i created a database table with 5 columns. With the insert query i am inserting values into 2 columns and i want that with this insert, the values of other 3 columns should be automatically be filled. For this purpose I thought to create a trigger which calculates sum and average of time and sales(two columns defined in database table respectively) i.e. when we enter time and sales, then sum and average (seperately for time and sales) should also get calculated and when we select the table then, it should show all the four entries(time,sales, sum,average).

Right now i am using trigger as:

ALTER TRIGGER [dbo].[trgInsert] ON [dbo].[tblEvalutionData]
    for insert
AS 
BEGIN
  insert into tblEvalutionData (AverageSales,TotalSales,TotalTime) 
  select avg(Sales) as AverageSales, sum(Sales) as TotalSales,sum(ElapsedTime)as  TotalTime from inserted
END

When i write this query

Insert into tblName(time,sales) values(5,34) and execute this it gives the result as:

Time Sales SumSales  AverageSales
5     34   null  null
null  null 34    34
6     14   null  null
null null  48    24

How can i resolve this? Or is there any other approach to do this?

Upvotes: 0

Views: 2046

Answers (1)

JNK
JNK

Reputation: 65197

Don't use a trigger for this!

SQL Server supports calculated columns. For aggregates you can use indexed views.

However, I think you are VERY confused about what you are trying to do.

If you want to aggregate a single inserted row, I can promise you that the SUM and AVERAGE will be equal to the Sales field.

Please provide more info on exactly what you are trying to accomplish (just edit the question to add it) and we can likely provide an answer.

Upvotes: 2

Related Questions