Sql-server – Identify faulty sub-query (subquery returned more than 1 value)

SET @ID = (SELECT ID FROM INSERTED)
SET @ID_COMPETENCIA = (SELECT ID_COMPETENCIA FROM INSERTED)

It's a common misconception that triggers fire once per row affected, when in fact a trigger body is only fired once per DML statement **.

In the trigger body, the inserted and deleted tables contain all the rows affected by the operation. This is done for (at least) two reasons:

1. Logical operations. Performing a task within the set of affected rows would be extremely painful if only a single row was available to the trigger body.
2. Performance. Efficient set-based operations can be used to process all of the affected rows at once.

Single-row trigger code can go unnoticed for a long time because it may legitimately work for your use cases, and only break when someone decides to affect more than a single row. Even worse, it's possible that the trigger code may actually succeed, and end up doing the wrong thing with no error.

Unfortunately, there are no safeties to tell you this information when writing triggers, so consider yourself lucky to have found this bug!

_{** For people reading this with SQL Server 2008+ in mind, MERGE fires the 3 different types separately, as it's really just a transactional "macro" to doing INSERT + UPDATE + DELETE in the same T-SQL statement.}

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 < 100000 THEN 5 ELSE 6 END)
      + '-' 
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.id), 
        CASE WHEN i.id < 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).