Subquery returned more than 1 value
Your trigger has no WHERE clause, does not correlate to the row(s) that were just inserted, and doesn't handle the case where multiple rows might be inserted in a single operation (unlike some platforms, in SQL Server a trigger fires per statement, not per row). So it will work exactly once: when you insert the very first row into the projects table. Try the following instead:

CREATE TRIGGER dbo.create_proid -- please always use schema prefix
ON dbo.projects 
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE p
    SET pro_id = CONVERT(VARCHAR(20), c.comp_type)
      + '-' 
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.client_id), 
        CASE WHEN i.client_id &lt; 100000 THEN 5 ELSE 6 END)
      + '-' 
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.id), 
        CASE WHEN i.id &lt; 100000 THEN 5 ELSE 6 END)
  FROM dbo.projects AS p
  INNER JOIN inserted AS i 
  ON p.id = i.id
  INNER JOIN dbo.comp_types AS c
  ON p.comp_type_id = c.comp_type_id;
END


Not sure why you're storing the pro_id value when you can determine it at runtime. Do you also have an update trigger that maintains it when the comp_type_id changes? What if a comp_type is deleted?

Also please always use schema prefix and don't convert to NVARCHAR without specifying a length. And why are you using the text data type? This has been deprecated for ages - you should be using NVARCHAR(MAX) or VARCHAR(MAX) only if these names and descriptions will really exceed 4000/8000 characters (highly unlikely from the naming).