CODEWITHSUNDEEP
sqlsql-server
tomdertech
tomdertech

Reputation: 507

SQL data processing

I have an MS SQL database where sensor data is being streamed from a low level device, into say column 1.

In columns 2 and 3 I have some alarm levels.

What I would like to do is for "some script" to test the data as it arrives in column 1 against the levels in columns 2 and 3 and place a boolean decision in column 4.

My question is can I have some SQL script to do this? Where would it reside or does my low level device need knowledge of the column 2 and 3 trip points and when it posts the data to column 1 it also posts the decision to column 4?

UPDATE - First Test

I have implemented this and tested in sqlfiddle (but I am unable to login for some reason) so here is my schema:

CREATE TABLE SensorData(
  data1 float NULL,
  data1alarm bool NOT NULL DEFAULT FALSE
);  
//
CREATE TABLE SensorConfig(
  data1max float NULL,
  data1min float NULL
);  
//
CREATE TRIGGER Alarm
AFTER INSERT 
ON SensorData FOR EACH ROW
BEGIN
  IF (new.data1 > 5.0) AND (new.data1 < 60.0) THEN
    INSERT INTO SensorData(data1alarm) 
    VALUES(true);
  END IF;
END
//
INSERT INTO SensorConfig(data1max, data1min) VALUES (200.0, 100.0);
INSERT INTO SensorData(data1) VALUES (10.0);

When INSERT INTO SensorData(data1) VALUES (10.0); I get the error:

Can't update table 'sensordata' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

but when INSERT INTO SensorData(data1) VALUES (1.0); then the schema builds.

I cannot figure out the problem.

Also, I want really to implement such a condition that a true is written when data1 is greater data1max and less than data1min.

But at the moment I not sure why the schema does not build?

Furthermore, when I have tried getting the data1min and data1max into the conditional part of the trigger I have encountered errors:

IF (new.data1 > SensorConfig.data1min) AND (new.data1 < SensorConfig.data1max) THEN

Any help would be much appreciated.

Upvotes: 1

Views: 1532

Answers (3)

Burleigh Bear
Burleigh Bear

Reputation: 3314

I would recommend that you don't insert things into your table, but rather create a view that contains the logic for the alarm codes. So you might do

create view alarming_data as
select 
    data1.thevalue as theValue
    true as theAlarm
from
    data1 where data1.theValue > 5 and data1.theValue < 60

That keeps your raw data "pure" and your processing separate. In fact, you don't really need the second field, I'm just putting it there to keep my answer as close to your example as possible.

You can query this view as you would a table, and if your definition of alarming changes, it's just a matter of updating the definition of the view.

Upvotes: 1

Simone
Simone

Reputation: 1924

You could create a trigger for that table with option AFTER INSERT

CREATE TRIGGER trDoMagic
ON YourTable
AFTER INSERT
   your magic code

Upvotes: 0

Mureinik
Mureinik

Reputation: 311843

If you only want to display the offending lines, you don't need to persist column4, you can query them dynamically:

SELECT *
FROM   mytable
WHERE  column1 > column2 OR column1 > column3

Upvotes: 0

Related Questions