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 COALESCE(SUM(number_of_items), 0) FROM credits WHERE user_id = NEW.user_id AND expiration >= datetime('now'))
         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 years'));
✓
INSERT INTO credits VALUES(1, 2, datetime('now', '-2 years'));
✓
INSERT INTO users_items VALUES(1, 100);

{
  "code": "SQLITE_CONSTRAINT_TRIGGER"
}


INSERT INTO credits VALUES(1, 3, datetime('now', '2 years'));
✓
INSERT INTO users_items VALUES(1, 100),(1, 101),(1, 102);
✓
INSERT INTO users_items VALUES(1, 104);

{
  "code": "SQLITE_CONSTRAINT_TRIGGER"
}


SELECT * FROM users;


user_id
channel_id


1
11

2
12

4
14


SELECT * FROM credits;


user_id
number_of_items
expiration


1
1
2023-03-10 11:26:16

1
2
2023-03-10 11:26:16

1
3
2027-03-10 11:26:16


SELECT * FROM users_items;


user_id
item_id


1
100

1
101

1
102

user_id	number_of_items	expiration
1	1	2023-03-10 11:26:16
1	2	2023-03-10 11:26:16
1	3	2027-03-10 11:26:16