How to update multiple records after insert/update in SQL Trigger?

Question

I have below query, which updates timestamp for 10 records.

UPDATE [dbo].[file] SET timestamp = GETDATE() WHERE id <= 10

I created a trigger on that table Insert/Update.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_file] 
 ON [dbo].[file]
 AFTER UPDATE
AS 
BEGIN
 SET NOCOUNT ON;
 DECLARE @ID BIGINT;
 DECLARE @RES VARCHAR(100);
 SET @ID = (select id from INSERTED);
 SET @RES = (select timestamp from INSERTED);
 IF @RES IS NOT NULL
 DELETE FROM [dbo].[file] WHERE id IN (@ID)
END

It gives error

Msg 512, Level 16, State 1, Procedure trg_file
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.

Seems the issue is here as it's getting multiple records at the time.

 SET @ID = (select id from INSERTED);

How to resolve it? Via Cursor I seen some of the post.

I want to apply my trigger on multiple value update.

Which is the best solution for this?

• Akina: For example, SET @ID = (select MAX(id) from INSERTED);
• Jack Molley: HI @Akina, I tried above ones. Not giving error, but updating only last record, instead of all.

Answer 1

You need to make sure whatever you are doing in the trigger can handle multiple rows. You can limit the @ID value to just a single modified value, as Akina suggests, but that is probably not what you want.

You don't have any actual logic inside the example trigger you sent, so I've included an example here of what could be done using the INSERTED table allowing for a multiple row modification.

CREATE TABLE dbo.File 
 (
 ID INT NOT NULL IDENTITY(1,1)
 , FileName NVARCHAR(200) NULL
 , timestamp DATETIME2(7) NULL
 , AfterUpdateValue NVARCHAR(200) NULL
 )
GO
CREATE TRIGGER trgFile_SetAfterUpdateValue
ON dbo.File
AFTER UPDATE
AS
BEGIN
 SET NOCOUNT ON
 UPDATE F
 SET AfterUpdateValue = REVERSE(I.FileName)
 FROM dbo.File AS F
 INNER JOIN INSERTED AS I ON I.ID = F.ID 
END

To copy the logic you have established above, this example shows one way of doing that.

CREATE TRIGGER trgFile_SetAfterUpdateValue
ON dbo.File
AFTER UPDATE
AS
BEGIN
 SET NOCOUNT ON
 DELETE F 
 FROM dbo.File AS F
 INNER JOIN INSERTED AS I ON I.ID = F.ID 
 WHERE F.timestamp IS NOT NULL
END

When you are inside a trigger, you have access to two virtual tables. INSERTED and DELETED. They have the same columns as the base table. The INSERTED table has the values of the NEW (and if in an AFTER trigger, is equivalent to the CURRENT values). The DELETED table contains the PRIOR and DELETED rows.

If a row exists in the DELETED table but is not in the INSERTED table, then the row was deleted. You can compare the values of the DELETED and INSERTED tables to see changed values (make sure to account for NULL).

However, you need to be careful here, the INSERTED table contains all values, even if the timestamp one isn't changing, so you need to do a little bit more logic to make sure you only delete when the timestamp value actually changes.

This is one of the better ways (in my experience) to accomplish this.

;WITH CTE_ChangedTimestamp AS
 (
 SELECT I.ID, I.timestamp
 FROM INSERTED AS I
 EXCEPT
 SELECT D.ID, D.timestamp
 FROM DELETED AS D
 )
 DELETE F
 FROM dbo.File AS F
 INNER JOIN CTE_ChangedTimestamp AS C ON C.ID = F.ID 

This will detect any change to the timestamp value. It will allow the timestamp value to be set when the row is first created, however, it will NOT let the value be changed after that (even if it is created with a NULL value).

• Jack Molley: Hi, I updated question. On update of that field, if the value is changed then I want do an operation, like delete records, etc..