Nobody replied, but I found a solution. INSERTED is effectively a temporary table. You have to loop over this table with a cursor
Not an ideal solution, but in this case I am dealing with only a small number (1-5) of inserts each time, so there is no noticeable overhead. Richard Meredith-Hardy wrote: > > Dear all > > Rather than looping over a list to insert multiple log rows, it occurred > to me that I could have a 'dummy' column in the master table so one > inserts all logs at once thus: > > UPDATE Mymaster SET mydummy = 4 WHERE mykey IN(5l,67,21) > > and then have a trigger in the 'Mymaster' table which lets SQL Server > actually insert the row in the log in its own time thus: > > FOR UPDATE > AS > IF UPDATE(mydummy) > INSERT INTO [dbo].[mylog] > (mykey,mydummy) > SELECT mykey, mydummy FROM INSERTED > > This works fine if there is only one value in the IN() clause but fails > if there is more than one; "Subquery returned more than 1 value" > > I had slightly assumed triggers work on a row by row basis, but this > would suggest they might not. I am sure there is a simple way around > this. Can anyone help please? > > (this is CF5 by the way) > > -- > Regards; > > Richard Meredith-Hardy > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
