By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,569 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,569 IT Pros & Developers. It's quick & easy.

Multiple Update Trigger Error.

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

Update -
Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER PROCEDURE [dbo].[uspOverviewUpdateCancelledFlag]
  3.     @ApplicationId int,
  4.     @Cancelled bit,
  5.     @LastUpdatedBy varchar(151)
  6.  
  7. AS
  8.  
  9. -- SET NOCOUNT ON added to prevent extra result sets from
  10. -- interfering with SELECT statements.
  11. SET NOCOUNT ON;
  12.  
  13. BEGIN
  14.     UPDATE dbo.tblOverview SET
  15.         cancelled = @Cancelled,
  16.         last_updated_by = @LastUpdatedBy,
  17.         last_updated_date = GETDATE(),
  18.         revision = revision + 1
  19.     WHERE
  20.         application_id = @ApplicationId
  21.     AND    ((overview_type_id = 1) OR (overview_type_id = 2 AND requirement_id != 13))    
  22.  
  23. END
  24.  
  25. RETURN
  26.  
  27.  
Trigger -

Expand|Select|Wrap|Line Numbers
  1. declare @Changes varchar(max)
  2. set @Changes = ''
  3.  
  4.         if ((select application_id from deleted) != (select application_id from inserted)  or 
  5.             (select application_id from deleted) IS NULL and (select application_id from inserted) IS NOT NULL)
  6.             set @Changes = @Changes + 'application_id  from [' + ISNULL(convert(varchar(100),(select 
  7.         application_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select application_id from inserted)),'Null') + '] '
  8.  
  9.         if ((select overview_type_id from deleted) != (select overview_type_id from inserted)  or 
  10.             (select overview_type_id from deleted) IS NULL and (select overview_type_id from inserted) IS NOT NULL)
  11.             set @Changes = @Changes + 'overview_type_id  from [' + ISNULL(convert(varchar(100),(select 
  12.         overview_type_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select overview_type_id from inserted)),'Null') + '] '
  13.  
  14.         if ((select requirement_id from deleted) != (select requirement_id from inserted)  or 
  15.             (select requirement_id from deleted) IS NULL and (select requirement_id from inserted) IS NOT NULL)
  16.             set @Changes = @Changes + 'requirement_id  from [' + ISNULL(convert(varchar(100),(select 
  17.         requirement_id from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select requirement_id from inserted)),'Null') + '] '
  18.  
  19.         if ((select deadline_date from deleted) != (select deadline_date from inserted)  or 
  20.             (select deadline_date from deleted) IS NULL and (select deadline_date from inserted) IS NOT NULL)
  21.             set @Changes = @Changes + 'deadline_date  from [' + ISNULL(convert(varchar(100),(select 
  22.         deadline_date from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select deadline_date from inserted)),'Null') + '] '
  23.  
  24.         if ((select description from deleted) != (select description from inserted)  or 
  25.             (select description from deleted) IS NULL and (select description from inserted) IS NOT NULL)
  26.             set @Changes = @Changes + 'description  from [' + ISNULL(convert(varchar(100),(select 
  27.         description from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select description from inserted)),'Null') + '] '
  28.  
  29.         if ((select completed from deleted) != (select completed from inserted)  or 
  30.             (select completed from deleted) IS NULL and (select completed from inserted) IS NOT NULL)
  31.             set @Changes = @Changes + 'completed  from [' + ISNULL(convert(varchar(100),(select 
  32.         completed from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select completed from inserted)),'Null') + '] '
  33.  
  34.         if ((select cancelled from deleted) != (select cancelled from inserted)  or 
  35.             (select cancelled from deleted) IS NULL and (select cancelled from inserted) IS NOT NULL)
  36.             set @Changes = @Changes + 'cancelled  from [' + ISNULL(convert(varchar(100),(select 
  37.         cancelled from deleted)),'Null') + '] to [' + ISNULL(convert(varchar(100),(select cancelled from inserted)),'Null') + '] '
  38.  
  39. if (@Changes != '')
  40. BEGIN
  41. INSERT INTO dbo.tblAudit
  42. (
  43.     table_name,
  44.     row_id,
  45.     row_revision,
  46.     operation,
  47.     user_name,
  48.     date_time,
  49.     changes,
  50.     application_reference
  51. )
  52. select    'tblOverview',
  53.     inserted.id,
  54.     inserted.revision,
  55.     'Update',
  56.     inserted.last_updated_by,
  57.     inserted.last_updated_date,
  58.     @Changes,
  59.     application_reference
  60. from    inserted INNER JOIN
  61. tblapplication on tblapplication.id = application_id
  62.  
  63. END
  64.  
  65.  
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.
Sep 1 '10 #1

✓ answered by ck9663

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

What do you mean by:

Expand|Select|Wrap|Line Numbers
  1. (select application_id from deleted) != (select application_id from inserted)
  2.  
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

Share this Question
Share on Google+
3 Replies


100+
P: 201
what i dont see before the subquery or after is that values clause.

insert into table_name(columns) values (columns data)
Sep 1 '10 #2

ck9663
Expert 2.5K+
P: 2,878
Is this code working before and you just experience the error recently?

What do you mean by:

Expand|Select|Wrap|Line Numbers
  1. (select application_id from deleted) != (select application_id from inserted)
  2.  
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
Sep 1 '10 #3

P: 4
I rewrote the procedure that updates the table so that it updates each record separately, then the trigger is actioned each time.
Sep 7 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.