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(colu mns) 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.