How to write trigger for multiple row update?

Question

I have written an update trigger which works fine when i update only one row but gives an error when i updated multiple rows.

Error:

Msg 512, Level 16, State 1, Procedure Sale_OnUpdate, Line 14 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here is Trigger

ALTER  TRIGGER [dbo].[Sale_OnUpdate]    ON  [dbo].[Sale] 
   AFTER Update

AS 
Declare @ID as decimal
Declare @User as varchar(250)
Declare @Status as varchar(250)

set @ID = (Select ID from Inserted)
set @User = (Select UpdatedByUser from Inserted)
set @Status = Isnull((Select Status from Inserted),'')

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   INSERT INTO [dbo].[Log]
           (
           [RecordID]
           ,[Date]
           ,[Time]
           ,[UserName]
           ,[TableName]
           ,[Action]
           )
     VALUES
           (
           @ID
           ,GetDate()
           ,GetDate()
           ,@User
           ,'Sale'
           ,'Update,' + @Status
           )
END

What change should i do to make it working for multiple rows.

John Pick · Accepted Answer · 2012-02-29 05:06:00Z

1

ALTER  TRIGGER [dbo].[Sale_OnUpdate]    ON  [dbo].[Sale] 
   AFTER Update
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   INSERT INTO [dbo].[Log]
           (
           [RecordID]
           ,[Date]
           ,[Time]
           ,[UserName]
           ,[TableName]
           ,[Action]
           )
     SELECT
           ID
           ,GetDate()
           ,GetDate()
           ,UpdatedByUser
           ,'Sale'
           ,'Update,' + Isnull(Status,'')
     from Inserted
END

answered Feb 29, 2012 at 5:06

John Pick

5,69033 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mujassir Nasir Over a year ago

But i want to get these three fields (ID,User,Status) to log.

John Pick Over a year ago

@MujassirNasir That's exactly what the revised trigger does (except that UpdatedByUser is used instead of User, as specified in your original question). Carefully inspect the SELECT clause.

Ghost Answer · Accepted Answer · 2012-02-29 05:39:59Z

try this

ALTER TRIGGER [dbo].[Sale_OnUpdate] ON [dbo].[Sale] 
  AFTER Update

AS 
as 
if update (qty) 


BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
  SET NOCOUNT ON;
/* check value of @@rowcount */ 
    if @@rowcount = 1 

  INSERT INTO [dbo].[Log]
    ([RecordID]
   , [Date]
   , [Time]
   , [UserName]
   , [TableName]
   , [Action])
  SELECT id,
         GETDATE(),
         GETDATE(),
         updatedbyuser,
         'Sale',
         'Update,' + ISNULL(status, '')

    else
    /* when rowcount is greater than 1, 
       use a group by clause */ 
    begin 
    INSERT INTO [dbo].[Log]
    ([RecordID]
   , [Date]
   , [Time]
   , [UserName]
   , [TableName]
   , [Action])
  SELECT i.id,
         GETDATE(),
         GETDATE(),
         i.updatedbyuser,
         'Sale',
         'Update,' + ISNULL(i.status, '')
    FROM INSERTED i
    and inserted.title_id = deleted.title_id
   end

END

OMG Ponies · Accepted Answer · 2012-02-29 05:04:25Z

0

Use:

ALTER TRIGGER [dbo].[Sale_OnUpdate] ON [dbo].[Sale] 
  AFTER Update

AS 

BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
  SET NOCOUNT ON;

  INSERT INTO [dbo].[Log]
    ([RecordID]
   , [Date]
   , [Time]
   , [UserName]
   , [TableName]
   , [Action])
  SELECT i.id,
         GETDATE(),
         GETDATE(),
         i.updatedbyuser,
         'Sale',
         'Update,' + ISNULL(i.status, '')
    FROM INSERTED i

END

answered Feb 29, 2012 at 5:04

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

6 Comments

Mujassir Nasir Over a year ago

But i want to get these three fields (ID,User,Status) to log.

OMG Ponies Over a year ago

@MujassirNasir: That wasn't your question.

Mujassir Nasir Over a year ago

I have tried this i know it works but i want to write trigger to log every action.

OMG Ponies Over a year ago

@MujassirNasir: There's no limit, that I'm aware of, for asking separate questions.

John Pick Over a year ago

@MujassirNasir: What do you mean by "every action"? The OMGPonies trigger logs the same number of records that were updated in Sales. Isn't that what you need?

|

Community · Accepted Answer · 2017-05-23 10:34:14Z

0

I had a similar problem, the idea here is that INSERTED table have many records, so if you want analyze each value, you will need to iterate the table using a cursor by example:

Loop through INSERTED table

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Oct 8, 2016 at 19:53

Jairo Martínez

5556 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to write trigger for multiple row update?

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related