Help - View Trigger or Table Trigger

Hello All,
My application takes data readings every few seconds and stores a
couple of data values for piece of equipment in a table for historic
trending. The table is simlar to:
CREATE TABLE [DataTest] (
[DataID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EquipmentID] [int] NOT NULL,
[DataTimestamp] [datetime] NOT NULL ,
[DataReading1] [decimal](18, 4) NOT NULL,
[DataReading2] [decimal](18, 4) NOT NULL
[DataReading3] [decimal](18, 4) NOT NULL
)
My application needs to alert users when data values fall outside of
acceptable thresholds. It also must allow users to view a history of
when these "alerts" have triggered.
I would like to utilize the database (SQL Server 2000) as much as
possible for this functionality. I originally thought I could create a
new View for each alert that users set. For example,
CREATE VIEW dbo.Alert_25
AS
SELECT * FROM DataTest WHERE DataReading1 > 200 AND DataReading2 < 500
WHERE EquipmentID = 1
Each view would then contain the history of when that alert triggered.
I then thought I could add a trigger to that View to notify the user
that the data has gone out of range. This is where things fall apart.
The inserts are happening on the main table not the view, so a trigger
on the view does not fire.
SO...here are my questions.
1. I would like to keep the trigger associated with the View if
possible. That way, if the user deletes the View the triggers can
easily be removed as well. Is there anyway for the trigger to fire if
it is on the View?
2. I guess my alternative would be to have both Views for the alert
histories and multiple triggers on the data table. Is this an
acceptable approach? Say the user where to create 100 different alerts.
What impact on the server would this have if this created 100 different
triggers on the same data table?
I appreciate your opinions and advice. If I am way off base all
together, I appreciate any other suggestions.
Thanks,
YofnikOn 15 Aug 2005 13:09:39 -0700, Yofnik wrote:
(snip)
>SO...here are my questions.
>1. I would like to keep the trigger associated with the View if
>possible. That way, if the user deletes the View the triggers can
>easily be removed as well. Is there anyway for the trigger to fire if
>it is on the View?
Hi Yofnik,
No. A view can have only an INSTEAD OF trigger defined on it, and that
will only fire when an insert, update or delete is executed with the
view as target.

>2. I guess my alternative would be to have both Views for the alert
>histories and multiple triggers on the data table. Is this an
>acceptable approach? Say the user where to create 100 different alerts.
>What impact on the server would this have if this created 100 different
>triggers on the same data table?
It would cause a dramatic slowdown of your data modifications. Keeping
it all in one trigger would also cause a slowdown, but not as much.
Anyway, I think you should also reconsider the first stetp. You mention
"alerting users" without going into the specifics, but I have a hunch
that you plan to send out an email. And sending email from a trigger is,
as Orwell would say, doubleplusungood practice. Not only because it will
slow down your inserts tremendously, but also becuase you are mingling
in-transaction and out-transaction actions. What happens if the
transaction fails and is rolled back immediately after the mail is sent?
Your best bet is probably to investigate what Notification Services can
do for you.
If NS can't be used in your case, then create your views, write a script
that checks for entries in each of the views without matching entry in
the "alert history" table and sends a mail if it finds one. Use Agent to
schedule this as a job that runs as often as you need it (depends on how
much time may pass between the measurement and the alert).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I suppose I did not mention HOW the trigger would alert users. I
definitely was not planning on sending an email from the trigger. I
would like the trigger to simply put a new row in the AlertHistory
table with the AlertID and a flag indicating if it has been processed.
Then an external application can regularly poll this one table and grab
all alert IDs that have not been processed and send out an email. The
external application will then set the flag to indicate the alerts have
been processed.
With that said, is your last suggestion my best approach? Is there an
easy way to check for new rows in ALL views? I guess that would require
dynamic SQL then, huh?|||On 15 Aug 2005 13:42:25 -0700, Yofnik wrote:

>Sorry, I suppose I did not mention HOW the trigger would alert users. I
>definitely was not planning on sending an email from the trigger. I
>would like the trigger to simply put a new row in the AlertHistory
>table with the AlertID and a flag indicating if it has been processed.
>Then an external application can regularly poll this one table and grab
>all alert IDs that have not been processed and send out an email. The
>external application will then set the flag to indicate the alerts have
>been processed.
>With that said, is your last suggestion my best approach? Is there an
>easy way to check for new rows in ALL views? I guess that would require
>dynamic SQL then, huh?
Hi Yofnik,
If you only want to insert a row in the AlertHistory table, I'd probably
go for a trigger. But just ONE trigger - nod hundreds of 'em!
Either hard-code the limits that will cause an alert in the trigger (and
make sure that allchange requests go through one channel - i.e. you). If
you must have flexibility for the ussers to add, remove or change the
upper and lower limits for alerts, than create a second table to hold
all the limits, and join to that in the trigger to determine if any
alerts are generated. If you need more help on how to design this table
and how to code the trigger, give us some more information on your
current tables and data. Check out www.aspfaq.com/5006 to find out what
inforamtion we need to best address your question.
WRT your last question - steer clear of dynamic SQL! If you want to know
why, read http://www.sommarskog.se/dynamic_sql.html.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)