Блог

У меня есть база данных с двумя таблицами:

• устройства
• температура

Схема выглядит следующим образом:

 CREATE TABLE IF NOT EXISTS devices(  device_id serial PRIMARY KEY,  device_name varchar(255) UNIQUE NOT NULL,  last_record_time timestamp without time zone DEFAULT '1995-10-30 10:30:00' );  CREATE TABLE IF NOT EXISTS temperature(  device_id integer NOT NULL,  temperature decimal NOT NULL,  record_time timestamp without time zone NOT NULL,  CONSTRAINT temperature_device_id_fkey FOREIGN KEY (device_id)  REFERENCES devices (device_id) MATCH SIMPLE  ON UPDATE NO ACTION ON DELETE NO ACTION );  

В таблице устройства хранится список всех устройств. Таким образом, для каждого устройства существует уникальный идентификатор. Таблица температуры объединяет данные со всех устройств. Вы можете выбрать по идентификатору устройства, чтобы просмотреть все записи, относящиеся к определенному устройству.

У меня есть ограничение, которое я не могу удалить из таблицы устройств, потому что от этого зависит таблица температур. Я также хотел бы, чтобы таблица устройств обновлялась при вставке новой записи в таблицу температуры.

То есть время записи из новой записи температуры должно стать временем последней записи для записи этого устройства в таблице устройств. Таким образом, я всегда знаю, когда устройство в последний раз вставляло данные.

В настоящее время я делаю это программно. Я вставляю записи, и сразу же выбираю их обратно и записываю в другую таблицу. Это вводит некоторые ошибки. Поэтому я бы предпочел автоматизировать это на уровне базы данных. Как я могу решить эту проблему?

Альтернативой использованию триггера будет CTE:

 WITH ins AS (  INSERT INTO temperature (device_id, temperature, record_time)  VALUES (1, 35.21, '2018-01-30 09:55:23')  RETURNING device_id, record_time ) UPDATE devices AS d SET last_record_time = ins.record_time FROM ins WHERE d.device_id = ins.device_id;  

используйте триггер, чтобы сделать это неявно.

создайте триггер в таблице температуры для таких событий, как вставка/удаление/обновление и обновление таблицы температуры внутри этого триггера.

 CREATE OR REPLACE FUNCTION update_last_record_time()  RETURNS trigger AS $ BEGIN  UPDATE devices  SET last_record_time = NEW.record_time  WHERE device_id = NEW.device_id;   RETURN NEW; END;  $ LANGUAGE 'plpgsql';  CREATE TRIGGER my_trigger AFTER INSERT ON temperature FOR EACH ROW EXECUTE PROCEDURE update_last_record_time();  

Для удаления строки из дочерней таблицы Используйте Каскадное удаление при создании внешнего ключа он автоматически удалит записи из дочерней таблицы, когда запись родительской таблицы будет удалена