我在一个表上应用了"删除后"触发器,下面是脚本:
ALTER TRIGGER [dbo].[onDelete_N_UR]
ON [dbo].[Notification_UnRead]
AFTER delete
AS
BEGIN
SET NOCOUNT ON;
declare @roid int
set @roid=(select ReachOutID from deleted(nolock)
where deleted.NotificaionType='reachoutlike')
update CACHE_Reachout
set CACHE_Reachout.LIKEcount=(select [dbo].[getReachout_Notification_Count](@roid,'like') )
where CACHE_Reachout.ReachOutID=@roid
结束
现在我正在尝试使用以下sql语句批量删除一些行:
delete from Notification_UnRead where Notification_ID=****
它给了我错误
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
当delete触发器应用于上面的delete语句时,如何使用它删除多行。
试试这个-
ALTER TRIGGER [dbo].[onDelete_N_UR]
ON [dbo].[Notification_UnRead]
AFTER DELETE
AS BEGIN
SET NOCOUNT ON;
DECLARE @roid INT
SET @roid =
(
SELECT TOP 1 ReachOutID
FROM DELETED d
WHERE d.NotificaionType = 'reachoutlike'
)
UPDATE CACHE_Reachout
SET CACHE_Reachout.LIKEcount = dbo.getReachout_Notification_Count(@roid, 'like')
WHERE CACHE_Reachout.ReachOutID = @roid
END
或者试试这个(更适合使用)-
ALTER TRIGGER [dbo].[onDelete_N_UR]
ON [dbo].[Notification_UnRead]
AFTER DELETE
AS BEGIN
SET NOCOUNT ON;
UPDATE t
SET LIKEcount = dbo.getReachout_Notification_Count(d.ReachOutID, 'like')
FROM CACHE_Reachout t
JOIN DELETED d ON t.ReachOutID = d.ReachOutID
WHERE d.NotificaionType = 'reachoutlike'
END
任一
set @roid=(select ReachOutID from deleted(nolock)
where deleted.NotificaionType='reachoutlike')
或
set CACHE_Reachout.LIKEcount=
(select [dbo].[getReachout_Notification_Count](@roid,'like') )
where CACHE_Reachout.ReachOutID=@roid
正在返回超过1行的数据。
Raj
ALTER TRIGGER [dbo].[onDelete_N_UR]
ON [dbo].[Notification_UnRead]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @roid INT
SET @roid =
(
SELECT TOP 1 ReachOutID
FROM DELETED(nolock)
WHERE DELETED.NotificaionType = 'reachoutlike'
)
UPDATE CACHE_Reachout
SET CACHE_Reachout.LIKEcount =
(
SELECT [dbo].[getReachout_Notification_Count](@roid, 'like')
)
WHERE CACHE_Reachout.ReachOutID = @roid
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[onDelete_N_UR]
ON [dbo].[Notification_UnRead]
AFTER delete
AS
BEGIN
SET NOCOUNT ON;
declare @roid int
CREATE TABLE #TempTable (ReachOutID INT)
INSERT INTO #TempTable (ReachOutID)
select ReachOutID from deleted(nolock) where deleted.NotificaionType='reachoutlike'
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT ReachOutID
FROM #TempTable
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @roid
WHILE @@FETCH_STATUS = 0
BEGIN
update CACHE_Reachout set CACHE_Reachout.LIKEcount=(select [dbo].[getReachout_Notification_Count](@roid,'like') ) where CACHE_Reachout.ReachOutID=@roid
FETCH NEXT
FROM @getAccountID INTO @roid
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
drop table #TempTable
END