I am still trying to get of triggers right, but when is working it seems the other starts (failing) again. Annoying.. but also educational 🙂
When i executed the following SQL in MS SQL server is completes successfully, but when it is executed in a AFTER UPDATE trigger it fails with the error
Msg 512, Level 16, State 1, Procedure
TR_PHOTO_AU, Line 37 Subquery returned
more than 1 value. This is not
permitted when the subquery follows =,
!=, <, <= , >, >= or when the subquery
is used as an expression.
UPDATE p2 SET p2.esb = '0' FROM ( SELECT TOP(5) p1.esb FROM SOA.dbo.photos_TEST p1 WHERE p1.esb = 'Q' ORDER BY p1.arrivaldatetime ASC ) p2
Why is it not allowed to use a subquery as an expression in a trigger? And is there a workaround ?
The solution had to be found in a complete different direction, i accidentaly defined my trigger as a after insert, update trigger instead of a after update trigger. The following trigger definition does work now
CREATE TRIGGER TR_PHOTO_AU ON SOA.dbo.photos_TEST AFTER UPDATE AS DECLARE @MAXCONC INT -- Maximum concurrent processes DECLARE @CONC INT -- Actual concurrent processes SET @MAXCONC = 1 -- 1 concurrent processes -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON -- If column esb is involved in the update, does not necessarily mean -- that the column itself is updated If ( Update(ESB) ) BEGIN -- If column esb has been changed to 1 or Q IF ((SELECT esb FROM INSERTED) in ('1','Q')) BEGIN -- count the number of (imminent) active processes SET @CONC = (SELECT COUNT(*) FROM SOA.dbo.photos_TEST pc WHERE pc.esb in ('0','R')) -- if maximum has not been reached IF NOT ( @CONC >= @MAXCONC ) BEGIN -- set additional rows esb to '0' to match @MAXCONC UPDATE TOP(@[email protected]) p2 SET p2.esb = '0' FROM ( SELECT TOP(@[email protected]) p1.esb FROM SOA.dbo.photos_TEST p1 WHERE p1.esb = 'Q' ORDER BY p1.arrivaldatetime ASC ) p2 END END END
Can you try this? Make sure to specify all you your
PK columns as equal in the
WHERE inside the
UPDATE p1 SET p1.esb = '0' FROM SOA.dbo.photos_TEST p1 WHERE EXISTS ( SELECT TOP 5 * FROM SOA.dbo.photos_TEST p2 WHERE p1.<KEYFIELD> = p2.<KEYFIELD> AND p2.esb = 'Q' ORDER BY p1.arrivaldatetime ASC )