CODEWITHSUNDEEP
sqlite
BorisV
BorisV

Reputation: 699

SQLite: implementing reference counter in partitioned tables

I have 2 series of tables.

In series A each table is defined as (id_a TEXT PRIMARY KEY NOT NULL, id_b TEXT NOT NULL);

In series B each table defined as (id_b TEXT NOT NULL, value_b INT);

It is implied that series A bigger than series B (may contain more tables).

Also in series A the field id_b of each table may hold the value of some field id_b in series B only.

I want value_b in series B to be a reference counter - whenever in series A added/deleted row with some id_b, the value of value_b in series B will increase/decrease for corresponding id_b by number of affected rows.

How can it be implemented while keeping minimum diskspace/time consumption?

Upvotes: 0

Views: 30

Answers (1)

CL.
CL.

Reputation: 180192

This can be done with triggers:

CREATE TRIGGER count_insert
AFTER INSERT ON TableA
FOR EACH ROW
BEGIN
  UPDATE TableB
  SET value_b = value_b + 1
  WHERE id_b = NEW.id_b;
END;

CREATE TRIGGER count_update
AFTER UPDATE OF id_b ON TableA
FOR EACH ROW
BEGIN
  UPDATE TableB
  SET value_b = value_b - 1
  WHERE id_b = OLD.id_b;
  UPDATE TableB
  SET value_b = value_b + 1
  WHERE id_b = NEW.id_b;
END;

(And similarly for DELETE.)

Upvotes: 2

Related Questions