All,
I'm trying to write some SQL that will populate a custom property (called AlertsPerDay) in the Nodes table. What I want is a trigger to count the number of alerts for each Node in the past 24 hours (from the AlertLog table) , and store that variable (in the Nodes table). Then I can write an Advanced Alert to let me know if some site is getting a lot of alerts in a day, and do a What's Up With That???
I added the column AlertsPerDay to Nodes as an integer.
Here's the trigger text:
CREATE TRIGGER [trg_AlertsPerHour] ON dbo.Nodes
FOR INSERT, UPDATE
AS
DECLARE @NodeID AS int
SET @NodeID = (SELECT NODEID FROM NODES)
UPDATE NODES SET AlertsPerHour = (SELECT Count(*)
from AlertLog where AlertLog.LogDateTime >
(GetDate()-1)) WHERE
AlertLog.ObjectID = @NodeID
Which produced this errror message:
Msg 4104, Level 16, State 1, Procedure trg_AlertsPerHour, Line 6
The multi-part identifier "AlertLog.ObjectID" could not be bound.
Any ideas? A SQL dba I'm not.....
Don
