Multiple Update Trigger Error.

[Kev37Barrie]

I am trying to do a multi record update on a table but get an error from the update trigger.

Update -

 
ALTER PROCEDURE [dbo].[uspOverviewUpdateCancelledFlag]
    @ApplicationId int,
    @Cancelled bit,
    @LastUpdatedBy varchar(151)
 
AS
 
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
 
BEGIN
    UPDATE dbo.tblOverview SET
        cancelled = @Cancelled,
        last_updated_by = @LastUpdatedBy,
        last_updated_date = GETDATE(),
        revision = revision + 1
    WHERE
        application_id = @ApplicationId
    AND    ((overview_type_id = 1) OR (overview_type_id = 2 AND requirement_id != 13))    
 
END
 
RETURN
 
 

Trigger -

declare @Changes varchar(max)
set @Changes = ''
 
        if ((select application_id from deleted) != (select application_id from inserted)  or 
            (select application_id from deleted) IS NULL and (select application_id from inserted) IS NOT NULL)
            set @Changes = @Changes + 'application_id  from [' + ISNULL(convert(varchar(100),(select 
        application_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select application_id from inserted)),'Null') + '] '
 
        if ((select overview_type_id from deleted) != (select overview_type_id from inserted)  or 
            (select overview_type_id from deleted) IS NULL and (select overview_type_id from inserted) IS NOT NULL)
            set @Changes = @Changes + 'overview_type_id  from [' + ISNULL(convert(varchar(100),(select 
        overview_type_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select overview_type_id from inserted)),'Null') + '] '
 
        if ((select requirement_id from deleted) != (select requirement_id from inserted)  or 
            (select requirement_id from deleted) IS NULL and (select requirement_id from inserted) IS NOT NULL)
            set @Changes = @Changes + 'requirement_id  from [' + ISNULL(convert(varchar(100),(select 
        requirement_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select requirement_id from inserted)),'Null') + '] '
 
        if ((select deadline_date from deleted) != (select deadline_date from inserted)  or 
            (select deadline_date from deleted) IS NULL and (select deadline_date from inserted) IS NOT NULL)
            set @Changes = @Changes + 'deadline_date  from [' + ISNULL(convert(varchar(100),(select 
        deadline_date from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select deadline_date from inserted)),'Null') + '] '
 
        if ((select description from deleted) != (select description from inserted)  or 
            (select description from deleted) IS NULL and (select description from inserted) IS NOT NULL)
            set @Changes = @Changes + 'description  from [' + ISNULL(convert(varchar(100),(select 
        description from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select description from inserted)),'Null') + '] '
 
        if ((select completed from deleted) != (select completed from inserted)  or 
            (select completed from deleted) IS NULL and (select completed from inserted) IS NOT NULL)
            set @Changes = @Changes + 'completed  from [' + ISNULL(convert(varchar(100),(select 
        completed from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select completed from inserted)),'Null') + '] '
 
        if ((select cancelled from deleted) != (select cancelled from inserted)  or 
            (select cancelled from deleted) IS NULL and (select cancelled from inserted) IS NOT NULL)
            set @Changes = @Changes + 'cancelled  from [' + ISNULL(convert(varchar(100),(select 
        cancelled from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select cancelled from inserted)),'Null') + '] '
 
if (@Changes != '')
BEGIN
INSERT INTO dbo.tblAudit
(
    table_name,
    row_id,
    row_revision,
    operation,
    user_name,
    date_time,
    changes,
    application_reference
)
select    'tblOverview',
    inserted.id,
    inserted.revision,
    'Update',
    inserted.last_updated_by,
    inserted.last_updated_date,
    @Changes,
    application_reference
from    inserted INNER JOIN
tblapplication on tblapplication.id = application_id
 
END
 
 

The error I get when trying to update 2 records is -

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

Can anyone help.

Thanks

Kevin.

[wizardry]

what i dont see before the subquery or after is that values clause.

insert into table_name(columns) values (columns data)

[ck9663]

Is this code working before and you just experience the error recently?

What do you mean by:

(select application_id from deleted) != (select application_id from inserted)
 

If you're trying to compare the old and new value of the application_id, you're going to have JOIN the two tables.

Trigger fires once for every update statement, not every updated record.

Also, I don't think you're allowed to use set to assign a value to a variable if the value is coming from a query returning multiple record set. Here's more with that.

Happy Coding!!!

~~ CK

[Kev37Barrie]

I rewrote the procedure that updates the table so that it updates each record separately, then the trigger is actioned each time.