Re: How to ensure a log-entry is created based on state of data in other tables - Mailing list pgsql-sql

Hi.

 

I'm not sure what the best subject is for this email, but here are the requirements: When all activities for a given product has status='DONE' then an entry in activity_product_log should be created. This should be guaranteed somehow. If an activity for which there exists an entry in activity_product_log is set to status='NOT_DONE', then the entry in activity_product_log should be deleted.

 

Here's an example-schema:

DROP TABLE IF EXISTS activity_product_log;
DROP TABLE IF EXISTS activity;
DROP TABLE IF EXISTS product;

CREATE TABLE product
(
    id INTEGER primary key,
    name varchar not null unique
);

CREATE TABLE activity
(
    id serial primary key,
    name varchar not null unique,
    product_id integer not null references product(id),
    status varchar NOT NULL,
    CHECK (status IN ('NOT_DONE', 'DONE'))
);

CREATE TABLE activity_product_log
(
    id serial primary key,
    product_id integer not null references product(id),
    created TIMESTAMP NOT NULL
);

INSERT INTO product(id, name) VALUES(1, 'Horse'), (2, 'Fish');

INSERT INTO activity(name, product_id, status)
VALUES ('A1', 1, 'NOT_DONE'), ('A2', 1, 'NOT_DONE'), ('A3', 1, 'NOT_DONE'), ('A4', 1, 'NOT_DONE')
     , ('A5', 2, 'NOT_DONE'), ('A6', 2, 'NOT_DONE')
;

UPDATE activity SET status = 'DONE' WHERE name = 'A1';
UPDATE activity SET status = 'DONE' WHERE name = 'A2';
UPDATE activity SET status = 'DONE' WHERE name = 'A3';
UPDATE activity SET status = 'DONE' WHERE name = 'A4'; -- Here an entry in activity_log should be created

I'm thinking about using constraint-triggers for this but will that actually guarantee the requirements? I'm worried about the last part of the requirement, if all activities for a product are ‘DONE’ but then gets toggled ‘NOT_DONE’ and ‘DONE’ “very fast”.

 

Is there a better way, using some extra tables to do bookkeeping can the have constraints ensuring this business-requirement?