Question

Design a SQL Server trigger to calculate column averages and return them to another table

SQL Server performance, SQL Server development, Triggers

I'm quite new to SQL Server and I’m struggling to design a trigger. I need this trigger to execute daily and calculate averages of columns and return them to another table.

What you want is actually a stored procedure not a trigger. Triggers can not be fired off on schedule, while stored procedures can. First you need to write your select statement which simply returns the data that you need to load into the destination table. The query will look something like this.

SELECT IdField, AVG(NumericField)
 FROM Table
 GROUP BY IdField

You then turn this into an insert statement.

INSERT INTO AnotherTable
 SELECT IdField, AVG(NumericField)
 FROM Table
 GROUP BY IdField

You then wrap this in the stored procedure wrapper to save the procedure.

CREATE PROCEDURE MyProcedure
 AS
 INSERT INTO AnotherTable
 SELECT IdField, AVG(NumericField)
 FROM Table
 GROUP BY IdField
 GO

You can then setup a SQL Agent Job to run this procedure nightly.