SQLite: Using count in triggers to update another column
Your error i can't reproduce, but i think you want this for your trigger.
MAX(u_cnt) + 1 can also be used as long as the insert only uses 0 for u_cnt


CREATE TABLE rec_info 
    (&quot;id&quot; INTEGER, &quot;username&quot; TEXT(6), &quot;u_cnt&quot; 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 
    (&quot;id&quot;, &quot;username&quot;, &quot;u_cnt&quot;)
VALUES
    (1, 'MarkC', '0')
;




INSERT INTO rec_info 
    (&quot;id&quot;, &quot;username&quot;, &quot;u_cnt&quot;)
VALUES
    (2, 'PeterB', '0')
;




INSERT INTO rec_info 
    (&quot;id&quot;, &quot;username&quot;, &quot;u_cnt&quot;)
VALUES
    (3, 'MarkC', '0')
;




INSERT INTO rec_info 
    (&quot;id&quot;, &quot;username&quot;, &quot;u_cnt&quot;)
VALUES
    (4, 'MarkC', '0')
;




SELECT * FROM rec_info


id | username | u_cnt
-: | :------- | :----
 1 | MarkC    | 3    
 2 | PeterB   | 1    
 3 | MarkC    | 3    
 4 | MarkC    | 3    


db&lt;&gt;fiddle here