Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
what values are you trying to insert when it fails ? I looked at the fiddle and it works OK. –  Kin Jun 12 '13 at 18:51
    
Thank you for your comment. I'm trying to insert the exact same as the fiddle. –  Ben Z. Jun 12 '13 at 18:52
    
I am not getting any errors. Can you post a screenshot with the error ? –  Kin Jun 12 '13 at 18:55
    
Check this: SELECT comp_type FROM comp_types WHERE comp_type_id = comp_type_id you should be equaling to some value on the projs table as this will return all values in comp_types –  user16484 Jun 12 '13 at 18:59
1  
The error appears when the comp_types has more than 1 row. –  ypercube Jun 12 '13 at 19:08

1 Answer 1

up vote 4 down vote accepted

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

share|improve this answer
    
Thank you very much! I thought the trigger is per row, but this clears it up a lot. To answer your questions: I thought not specifying a length for nvarchar will auto-assign it to 30, did not know about the size of 1. The only reason text is being used is due to the unknown possible lengths of the strings (especially pro_descpt; product description). –  Ben Z. Jun 12 '13 at 19:10
    
@BenZ will product description really potentially exceed 8000 characters (or 4000 if you need Unicode support)? And if you need Unicode support (implied by the data type of pro_id), why is comp_type text and not ntext? –  Aaron Bertrand Jun 12 '13 at 19:13
    
There are no triggers yet for updates in the comp_types table, but these will be added. pro_id is being stored because this is how the table initially was created, now several other systems depend on this table which expect it to be stored. Our IT-team finds it easier to keep the table this way than to fix the older systems. Unicode support is there due to our database "administrator"'s fear of the future, but is not being used/needed right now. –  Ben Z. Jun 12 '13 at 19:19
    
Ok, then at the very least the requirements should match. You shouldn't have some columns with Unicode support and some without... –  Aaron Bertrand Jun 12 '13 at 19:22
    
Thank you. That makes perfect sense, and will be fixed. As you can see I am fairly new to DBA; just started working for a company where the 'production server = test server'. As you can imagine such is not the best learning environment. –  Ben Z. Jun 12 '13 at 19:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.