Sql-server – Subquery returned more than 1 value

sql servertrigger

In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table.

A fully working SQL Fiddle.
Please run this and you will see, it works.

However when I try to run it in SQL Server I receive the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Screenshot: http://i.stack.imgur.com/38rKV.jpg

All tables in the SQL Server are fresh, with no data (just like the fiddle).
What am I doing wrong?

Best Answer

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).

Related Question