Design a SQL Server trigger to calculate column averages and return them to another table
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 16, 2008 10:25 PM
UPDATED:
March 17, 2008 12:31 PM
Answer Wiki:
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.
<pre>SELECT IdField, AVG(NumericField)
FROM Table
GROUP BY IdField</pre>
You then turn this into an insert statement.
<pre>INSERT INTO AnotherTable
SELECT IdField, AVG(NumericField)
FROM Table
GROUP BY IdField</pre>
You then wrap this in the stored procedure wrapper to save the procedure.
<pre>CREATE PROCEDURE MyProcedure
AS
INSERT INTO AnotherTable
SELECT IdField, AVG(NumericField)
FROM Table
GROUP BY IdField
GO</pre>
You can then setup a SQL Agent Job to run this procedure nightly.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Specifically you may want to check out my Back To Basics series which I’ve just started on my blog. So far I’ve only covered the SELECT statement, but the other commands will be coming shortly.