Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
Всем доброго времени суток.
Товарищи, помогите с решением проблемы.
Суть. Надо написать LOG событий. Данные получаю по СОМ порту с разных устройств (контроллеров) и пишу в БД. Вывод данных, с помощью PHP и JS на странице.
Надо поймать изменение состояния данных и записать в LOG таблицу.
Как я решил проблему, НО она мне не нравится. Нужен короткий код.

Я создал входную таблицу "Т1" (83 колонки), и точно такую же "Т2"(83 колонки), и таблицу логов "Т3(3 колонки)".
Написал триггер (если коротко то ...)
BEGIN
       SET @x = (select ps_abk_ch1 from Т1) ;  
       SET @a = (select ps_abk_ch1 from Т2) ;
        .................
       SET @x83 = (select ps_abk_ch83 from Т1) ;  
       SET @a83 = (select ps_abk_ch83 from Т2) ;

   if ( @x !=  @a) then
     insert Т3 set ocher =  @x, ps_name='АБК',ps_ch = 'Датчик № 1',ps_shtamp =  @dat ;
     UPDATE   Т2 SET ps_abk_ch1 = @x;
    end if;

..................

END

И так, для 83 точек.
Подскажите, как можно короче описать сравнение ИЛИ смотреть перед тем, что меняется и записывать в Т3.
На стороне сервера в PHP и JS все решается быстро, а вот внутри БД, как?
2 авг 16, 19:11    [19489124]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
KRS544
Member

Откуда:
Сообщений: 497
если развернуть таблицу (83 строки) №датчика, значение - все будет гораздо проще
2 авг 16, 19:54    [19489257]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
Alexey_Ko
Подскажите, как можно короче описать сравнение ИЛИ смотреть перед тем, что меняется и записывать в Т3.
На стороне сервера в PHP и JS все решается быстро, а вот внутри БД, как?


есть оператор OUTPUT

Вы скидываете данные в буфер, затем делаете MERGE между буфером и постоянной таблицей и буфером и скидываете изменения в через OUTPUT в таблицу
+

	USE AdventureWorks2012;  
GO  
IF OBJECT_ID(N'Production.ZeroInventory', N'U') IS NOT NULL  
    DROP TABLE Production.ZeroInventory;  
GO  
--Create ZeroInventory table.  
CREATE TABLE Production.ZeroInventory (DeletedProductID int, RemovedOnDate DateTime);  
GO  
  
INSERT INTO Production.ZeroInventory (DeletedProductID, RemovedOnDate)  
SELECT ProductID, GETDATE()  
FROM  
(   MERGE Production.ProductInventory AS pi  
    USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
           JOIN Sales.SalesOrderHeader AS soh  
           ON sod.SalesOrderID = soh.SalesOrderID  
           AND soh.OrderDate = '20070401'  
           GROUP BY ProductID) AS src (ProductID, OrderQty)  
    ON (pi.ProductID = src.ProductID)  
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0  
        THEN DELETE  
    WHEN MATCHED  
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty  
    OUTPUT $action, deleted.ProductID) AS Changes (Action, ProductID)  
WHERE Action = 'DELETE';  
IF @@ROWCOUNT = 0  
PRINT 'Warning: No rows were inserted';  
GO  
SELECT DeletedProductID, RemovedOnDate FROM Production.ZeroInventory;  
   


https://msdn.microsoft.com/en-us/library/ms177564.aspx
2 авг 16, 20:13    [19489357]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
Как уже предлагали, таблицу развернуть. Вторая таблица вообще не нужна.
Журналировать триггером примерно так:
create table dbo.T
(
 sensor_id int not null,
 sensor_Value float not null,
 constraint PK_T primary key (sensor_id)
);

create table dbo.T_log
(
 log_id int not null identity,
 sensor_id int not null,
 sensor_Value float not null,
 log_Timestamp datetime2 not null constraint DF_T_Log__log_Timestamp default (sysdatetime()),
 constraint PK_T_log primary key (log_id)
);
go

create trigger dbo.trT_OnUpdate
on dbo.T
after update
as
begin
 set nocount on;

 insert into dbo.T_Log
  (sensor_id, sensor_Value)
  select
   i.sensor_id, i.sensor_Value
  from
   inserted i join
   deleted d on d.sensor_id = i.sensor_id
  where
   i.sensor_Value <> d.sensor_Value;
end;
go
2 авг 16, 21:02    [19489541]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
СПАСИБО большое. Сейчас включаю "мозги" (те, что остались :) ) и начинаю работать.
Еще раз спасибо!
3 авг 16, 07:26    [19490655]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
Да, товарищи. При развороте таблицы, все проще. НО. при записи исходной программой, она у меня с COM port и по modbus читает и пишет в БД, меняется код с
UPDATE ps_data SET ps_abk_ch1=Item(PS.ch1), ps_abk_ch2=Item(PS.ch2), ps_abk_ch3=Item(PS.ch3), ps_abk_ch4=Item(PS.ch4), ps_abk_ch5=Item(PS.ch5), ps_abk_ch6=Item(PS.ch6), 
            ps_abk_ch7=Item(PS.ch7), ps_abk_ch8=Item(PS.ch8), ps_abk_ch9=Item(PS.ch9), ps_abk_ch10=Item(PS.ch10),
            ps_abk_ch11=Item(PS.ch11), ps_abk_ch12=Item(PS.ch12), ps_abk_ch13=Item(PS.ch13), ps_abk_ch14=Item(PS.ch14), ps_abk_ch15=Item(PS.ch15), 
            ps_abk_ch16=Item(PS.ch16), ps_abk_ch17=Item(PS.ch17), ps_abk_ch18=Item(PS.ch18), ps_abk_ch19=Item(PS.ch19), ps_abk_ch20=Item(PS.ch20),
            ps_abk_ch21=Item(PS.ch21), ps_abk_ch22=Item(PS.ch22), ps_abk_ch23=Item(PS.ch23), ps_abk_ch24=Item(PS.ch24), ps_abk_ch25=Item(PS.ch25), 
            ps_abk_ch26=Item(PS.ch26), ps_abk_ch27=Item(PS.ch27), ps_abk_ch28=Item(PS.ch28), ps_abk_ch29=Item(PS.ch29), ps_abk_ch30=Item(PS.ch30),
            ps_abk_ch31=Item(PS.ch31), ps_abk_ch32=Item(PS.ch32), ps_abk_ch33=Item(PS.ch33), ps_abk_ch34=Item(PS.ch34), ps_abk_ch35=Item(PS.ch35),
            ps_abk_ch36=Item(PS.ch36), ps_abk_ch37=Item(PS.ch37), ps_abk_ch38=Item(PS.ch38), ps_abk_ch39=Item(PS.ch39), ps_abk_ch40=Item(PS.ch40),
            ps_abk_ch41=Item(PS.ch41), ps_abk_ch42=Item(PS.ch42), ps_abk_ch43=Item(PS.ch43), ps_abk_ch44=Item(PS.ch44), ps_abk_ch45=Item(PS.ch45), 
            ps_abk_ch46=Item(PS.ch46), ps_abk_ch47=Item(PS.ch47), ps_abk_ch48=Item(PS.ch48), ps_abk_ch49=Item(PS.ch49), ps_abk_ch50=Item(PS.ch50),
            ps_abk_ch51=Item(PS.ch51), ps_abk_ch52=Item(PS.ch52), ps_abk_ch53=Item(PS.ch53), ps_abk_ch54=Item(PS.ch54), ps_abk_ch55=Item(PS.ch55),
            ps_abk_ch56=Item(PS.ch56), ps_abk_ch57=Item(PS.ch57), ps_abk_ch58=Item(PS.ch58), ps_abk_ch59=Item(PS.ch59), ps_abk_ch60=Item(PS.ch60),
            ps_abk_ch61=Item(PS.ch61), ps_abk_ch62=Item(PS.ch62), ps_abk_ch63=Item(PS.ch63), ps_abk_ch64=Item(PS.ch64), ps_abk_ch65=Item(PS.ch65),
            ps_abk_ch66=Item(PS.ch66), ps_abk_ch67=Item(PS.ch67), ps_abk_ch68=Item(PS.ch68), ps_abk_ch69=Item(PS.ch69), ps_abk_ch70=Item(PS.ch70), 
            ps_abk_ch71=Item(PS.ch71), ps_abk_ch72=Item(PS.ch72), ps_abk_ch73=Item(PS.ch73), ps_abk_ch74=Item(PS.ch74), ps_abk_ch75=Item(PS.ch75), 
            ps_abk_ch76=Item(PS.ch76), ps_abk_ch77=Item(PS.ch77), ps_abk_ch78=Item(PS.ch78), ps_abk_ch79=Item(PS.ch79), ps_abk_ch80=Item(PS.ch80), 
            ps_abk_ch81=Item(PS.ch81), ps_abk_ch82=Item(PS.ch82), ps_abk_ch83=Item(PS.ch83)

на, сами понимаете какой. :(
Придется каждую переменную отдельно писать. Попробую, что в итоге будет работать быстрее, то и оставлю.
Есть еще идея. Побаловаться с "массивами", но встал вопрос. Как разделитель " ". Хоть у меня слово в 2 байта сейчас, но могут попасть и значения с плавающей запятой, а это 4 байта - 2 слова. Массив не прокатит.
3 авг 16, 07:38    [19490664]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Alexey_Ko
Да, товарищи. При развороте таблицы, все проще. НО.
"Разворот" можно ещё применить только для записи изменений в триггере - командой UNPIVOT
А основную таблицу оставить широкую.
3 авг 16, 08:54    [19490747]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
[quot alexeyvg]
Alexey_Ko
UNPIVOT

Да. Почитал синтаксис команды. Вполне подходит. Пробую сотворить "чудо"
3 авг 16, 09:36    [19490900]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
KRS544
Member

Откуда:
Сообщений: 497
Alexey_Ko, что будете делать, когда датчиков будет не 83, а 830?
3 авг 16, 11:14    [19491605]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
KRS544
Alexey_Ko, что будете делать, когда датчиков будет не 83, а 830?
В общем да, насчёт модели нужно сильно подумать...
Универсальной и удобной для такой задачи является концепция "узких" таблиц. Да, это будет медленно, но модель будет концептуально правильная, и с ней будет легко работать.
А падение скорости можно компенсировать оптимизацией вставки данных, типа, делать не один INSERT на каждый датчик, а один INSERT на все, типа insert values (...), (...), (...), (...), ...
Вот с UPDATE такого не получится.
Но я не знаю, что собственно за система, и зачем там эти UPDATE (для съёма показаний с датчиков? странно...)
3 авг 16, 11:40    [19491863]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
Вопрос понятен. В конечном результате должна получится SCDA - Web-Scad. Движок уже готов. Данные с приборов читаются и не перезагружая страницу обновляются. Графики рисуются.
По таблицам. Получается для каждого ID(имени в сети), своя таблица. 83 прибора - это самая большая таблица. ПС - пожарная сигнализация от одной системы БОЛИД. У меня разных около 10-15. Там мало точек управления.
За идею реализации кода - СПАСИБО.
Есть еще узкие места для оптимизации, но это не для этого форума.
3 авг 16, 14:04    [19493060]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
Alexey_Ko,

А вы думаете о том, что в будущей версии системы будет не 83 датчика, а скажем 107, потом 123 и т.д
3 авг 16, 14:10    [19493101]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
А как еще можно забрать данные с СОМ порта по протоколу MODBUS и отобразить на Web странице?
Программку писал на delphi сам (ОРС + MODBUS+ SQL)

К сообщению приложен файл. Размер - 101Kb
3 авг 16, 14:15    [19493150]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
Вот так показываю, что взять, что нет

К сообщению приложен файл. Размер - 114Kb
3 авг 16, 14:17    [19493177]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Все ваши вопросы явно не связаны с MSSQL и для них есть отдельные форумы.
3 авг 16, 14:18    [19493186]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Alexey_Ko
Member

Откуда:
Сообщений: 13
Гавриленко Сергей Алексеевич
Все ваши вопросы явно не связаны с MSSQL и для них есть отдельные форумы.

Вы не внимательно читали? БД -MSSQL
3 авг 16, 14:25    [19493237]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Alexey_Ko
Гавриленко Сергей Алексеевич
Все ваши вопросы явно не связаны с MSSQL и для них есть отдельные форумы.

Вы не внимательно читали? БД -MSSQL
Это вы невнимательно читаете. В ветке MSSQL не обсуждается разработка Web-страниц или программ на Delphi.
3 авг 16, 14:29    [19493275]     Ответить | Цитировать Сообщить модератору
 Re: Отследить изменение в таблице и запись измененного  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Alexey_Ko
Гавриленко Сергей Алексеевич
Все ваши вопросы явно не связаны с MSSQL и для них есть отдельные форумы.

Вы не внимательно читали? БД -MSSQL
А вопросы тем не менее не связаны с MSSQL :-)
PS "невнимательно" пишется слитно.
3 авг 16, 14:30    [19493282]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить