SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE rec_info 
    ("id" INTEGER, "username" TEXT(6), "u_cnt" TEXT(6))
;



create trigger count_user
after insert on rec_info
begin
  update rec_info 
      set u_cnt = ( select COUNT(*) from rec_info  where username = new.username) 
  WHERE username = new.username;
end
INSERT INTO rec_info 
    ("id", "username", "u_cnt")
VALUES
    (1, 'MarkC', '0')
;
INSERT INTO rec_info 
    ("id", "username", "u_cnt")
VALUES
    (2, 'PeterB', '0')
;
INSERT INTO rec_info 
    ("id", "username", "u_cnt")
VALUES
    (3, 'MarkC', '0')
;
INSERT INTO rec_info 
    ("id", "username", "u_cnt")
VALUES
    (4, 'MarkC', '0')
;
SELECT * FROM rec_info


id
username
u_cnt


1
MarkC
3

2
PeterB
1

3
MarkC
3

4
MarkC
3