J'ai appliqué le triggersur "Après suppression" sur une table, ci-dessous le script:
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
FIN
Maintenant, j'essaye de supprimer certaines lignes en masse en utilisant l'instruction sql suivante:
delete from Notification_UnRead where Notification_ID=****
Et ça me donne une erreur
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Comment puis-je supprimer plusieurs lignes en utilisant l'instruction de suppression ci-dessus lorsque le triggersur de suppression est appliqué.
Essaye celui-là –
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
Ou essayez ceci ( plus préférablement pour l'utilisation ) –
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
Non plus
set @roid=(select ReachOutID from deleted(nolock) where deleted.NotificaionType='reachoutlike')
Ou
set CACHE_Reachout.LIKEcount= (select [dbo].[getReachout_Notification_Count](@roid,'like') ) where CACHE_Reachout.ReachOutID=@roid
returnne plus d'une ligne de données.
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