Sql-server – Subquery returned more than 1 value. This is not permitted + Trigger Error SQL Server

sql servertrigger

This trigger will update Is_Confidential_or_VIP column to 1 or 0 based on the text inserted or updated into ConfidentialRegistrationFlag and VIPType columns :

create trigger [dbo].[Is_Confidential_or_VIP]
on  [EDB].[dbo].[Patient_Info]
after update,insert 
AS
BEGIN
    declare @type1 nvarchar(max)
    declare @type2 nvarchar(max)

    (select @type1 = ConfidentialRegistrationFlag from inserted)
    (select  @type2 = VIPType from inserted)

    declare @flag int

    if (@type2 like 'MDP-A' or @type2 like 'MDP-B' or @type1= 'confidential')
       set @flag= 1
    else 
       set @flag= 0

    update EDB.dbo.Patient_Info
    set Is_Confidential_or_VIP = @flag
    where EPN = (select EPN from inserted)
 END

I have a problem when enabling this trigger cannot insert data and error msg:

Subquery returned more than 1 value

Best Answer

Actually Triggers fire once rather than multiple times if update statement modify more than one row.

Your trigger trying to update multiple rows which means Inserted table will also have more than one row and when you try to set Flag values, it return you error.

Join your table's key with Inserted

CREATE TRIGGER [dbo].[Is_Confidential_or_VIP] ON [EDB].[dbo].[Patient_Info]
    AFTER UPDATE, INSERT
AS
    BEGIN

        UPDATE  patinfo
        SET     Is_Confidential_or_VIP = CASE WHEN VIPType = 'MDP-A'  --- 'Like' Replaced with '='
                                                   OR VIPType = 'MDP-B'
                                                   OR ConfidentialRegistrationFlag = 'confidential'
                                              THEN 1
                                              ELSE 0
                                         END                    
         -- select case VIPType = 'MDP-A' OR VIPType = 'MDP-B'  OR ConfidentialRegistrationFlag = 'confidential' then 1 else 1  END,*
        FROM    EDB.dbo.Patient_Info patinfo
                INNER JOIN inserted i ON patinfo.EPN = i.EPN

    END

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Sql-server – 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 < 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).

Best Answer

Related Solutions

MySQL using trigger instead of check

Sql-server – Subquery returned more than 1 value

Related Question