CODEWITHSUNDEEP
sqlpostgresql
user5063151
user5063151

Reputation:

UPDATE one table upon INSERT INTO another table

I have a database with two tables:

The schema follows:

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
);

The devices table keeps a list of all the devices. So there is a unique id for each device. The temperature table aggregates data from all of the devices. You can select by device_id to see all the entries that pertain to a specific device.

I have the constraint that I cannot delete from the devices table because the temperature table depends on it. I would also like the devices table to be updated when a new record is inserted into the temperature table.

That is, the record_time from a new record in the temperature should become the last_record_time for that device's entry in the devices table. That way I always know when was the last time a device inserted data.

I am currently doing this programmatically. I insert records, and immediate select them right back out and write into the other table. This is introducing some bugs. So, I would prefer to automate this at the database level. How can I go about resolving this?

Upvotes: 1

Views: 5150

Answers (3)

Nishad
Nishad

Reputation: 426

use trigger to do this implicitly.

create trigger on temperature table for events such as insert/delete/update and update temparature table inside that trigger.

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();

Upvotes: 0

Mayank
Mayank

Reputation: 393

For Deleting row from child table Use Cascaded delete when creating foreign key it will automatically delete records from the child table when Parent table record will be deleted

Upvotes: 0

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Alternative to using trigger would be 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;

Upvotes: 2

Related Questions