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

Tags:
SQL Server development
SQL Server performance
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.
ASKED: March 16, 2008  10:25 PM
UPDATED: October 18, 2013  12:46 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    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.
    65,450 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following