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 
    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
       set @flag= 0

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

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

Subquery returned more than 1 value

  • 8
    Your trigger has MAJOR FLAW in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your UPDATE statements affects 25 rows, you'll get the trigger fired once, but then Inserted and Deleted will each contain 25 rows. Which of those 25 rows will your code select? It's non-deterministic. You need to rewrite your trigger to take this into account!
    – marc_s
    Jan 15 '15 at 10:21

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]

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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