SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users(
	user_id INT,
  	channel_id INT NOT NULL UNIQUE,
  	PRIMARY KEY (user_id)
);
✓
CREATE TABLE credits(
	user_id INT,
  	number_of_items INT CHECK(number_of_items > 0),
  	expiration DATETIME,
  	PRIMARY KEY (user_id, number_of_items, expiration),
  	FOREIGN KEY (user_id) REFERENCES users (user_id)
);
✓
CREATE TABLE users_items(
	user_id INT,
  	item_id INT,
  	PRIMARY KEY (user_id, item_id),
  	FOREIGN KEY (user_id) REFERENCES users (user_id)
);
✓
CREATE TRIGGER check_has_enough_credits_to_monitor_item
BEFORE INSERT ON users_items
BEGIN
   SELECT
     CASE
       WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >
            (SELECT sum(number_of_items) FROM credits WHERE user_id = NEW.user_id)
         THEN RAISE (ABORT, 'No more items allowed')
     END;
END;
✓
INSERT INTO users VALUES (1, 11),(2, 12),(4, 14);
✓
INSERT INTO credits VALUES(1, 1, datetime('now', '-2 days'));
✓
INSERT INTO credits VALUES(1, 2, datetime('now', '-2 days'));
✓
INSERT INTO users_items VALUES(1, 100),(1, 101),(1, 102);
✓
INSERT INTO users_items VALUES(1, 104);
✓
SELECT * FROM users;


user_id
channel_id


1
11

2
12

4
14


SELECT * FROM credits;


user_id
number_of_items
expiration


1
1
2024-11-08 16:33:57

1
2
2024-11-08 16:33:57


SELECT * FROM users_items;


user_id
item_id


1
100

1
101

1
102

1
104


SELECT COUNT(*) FROM users_items WHERE user_id = 1


COUNT(*)


4


SELECT sum(number_of_items) FROM credits WHERE user_id = 1


sum(number_of_items)


3