我有以下查询,它更新 10 条记录的时间戳。
UPDATE [dbo].[file] SET timestamp = GETDATE() WHERE id <= 10
我在该表插入/更新上创建了一个触发器。
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
它给出了错误
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.
似乎问题就在这里,因为它同时获得了多个记录。
SET @ID = (select id from INSERTED);
如何解决呢?通过光标我看到了一些帖子。
我想将触发器应用于多个值更新。
哪个是最好的解决方案?