Mujassir Nasir Mujassir Nasir - 2 months ago 6
SQL Question

How to write trigger for multiple row update?

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.

Answer

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