question

Giuseppe avatar image
Giuseppe asked

Subquery returned more than 1 value ON Trigger For Update

HI to all. I am trying to perform the following functionality on a trigger for update. CREATE TRIGGER [dbo].[trg_FLM_HistoryForSingleField1] ON [dbo].[TblToFunctionalLocationMaster] FOR UPDATE AS BEGIN SET NOCOUNT ON -- DECLARATION DECLARE @newValue VARCHAR(255) DECLARE @oldValue VARCHAR(255) DECLARE @sSQL NVARCHAR(4000) -- CREATING TMP TABLE TO SAVE DATA TO BE LOGGED SELECT * INTO #INSLOG FROM INSERTED SELECT * INTO #DELLOG FROM DELETED IF UPDATE(FunctionalLocationOrig) BEGIN SELECT @newValue = (SELECT FunctionalLocationOrig FROM INSERTED) SELECT @oldValue = (SELECT FunctionalLocationOrig FROM DELETED) IF @newValue IS NULL SELECT @newValue = '' IF @oldValue IS NULL SELECT @oldValue = '' SELECT @sSQL = 'INSERT INTO [dbo].[TblToFunctionalLocationMaster_HST] ( ' + 'ID_FK, HstTimeStamp, HstAction, HstSQLUser, Field, OldValue, ' + 'NewValue) SELECT TblToFunctionalLocationMaster.ID, ' + 'RecAdmLastTime = GETDATE(), ''UPDATED'' As Action, ''' + SUSER_NAME() + ''', ''FunctionalLocationOrig'', ''' + @OldValue + ''', ''' + @NewValue + ''' FROM TblToFunctionalLocationMaster INNER JOIN #DELLOG AS I ' + 'ON TblToFunctionalLocationMaster.ID = I.ID' EXEC sp_executesql @sSQL DROP TABLE #INSLOG DROP TABLE #DELLOG END END THE TRIGGER SHOULD KEEP TRACE OF EVERY CHANGE PERFORMEND IN THE FIELD FunctionalLocationOrig. ON EACH UPDATE QUERY THAT MODIFIES THE FIELD FunctionalLocationOrig SQL RETRUNS THE FOLLOWING ERROR: Msg 512, Level 16, State 1, Procedure trg_FLM_HistoryForSingleField, Line 49 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
triggersubquery
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site works on votes. Please show any helpful answers by clicking on the thumbs up next to those answers. If one answer lead you to a solution indicate that by clicking on the the check mark next to that answer.
0 Likes 0 ·
Don't forget to vote on the answers you've received and mark one as a solution.
0 Likes 0 ·
sqlaj 1 avatar image
sqlaj 1 answered
Have you tried selecting the top 1 record from the table and then delete that record after you process. Then loop through and select the top 1 again until you have processed all the records.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have to recommend against any form of RBAR in a trigger. It's bad enough that most triggers double the processing time even when very well written.
0 Likes 0 ·
Thank you. Selecting top 1 it works perfeclty and getting old and new values in a single sql statement optimizes execution.
-1 Like -1 ·
If you do both of these suggestions, then you'll end up with some wrong data.
1 Like 1 ·
DO NOT USE THE TOP 1 METHOD FOR THIS TRIGGER! DO NOT USE RBAR IN A TRIGGER! DO NOT USE WHILE LOOPS IN A TRIGGER! DO NOT MAKE COPIES OF THE INSERTED AND DELETED TABLES IN TEMPORARY STRUCTURES! USE PROPERLY CONSTRUCTED SET BASED CODE LIKE THAT WHICH KEV RILEY POSTED!
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Triggers work on sets of data, and not row by row, as you have found out. Instead of trying to get the old and new values per row and build a sql statement, simply execute the insert as set-based query. It will perform better too. So the body of the trigger will be something like (sorry can't test at present) INSERT INTO [dbo].[TblToFunctionalLocationMaster_HST] ( ID_FK, HstTimeStamp, HstAction, HstSQLUser, Field, OldValue, NewValue) SELECT TblToFunctionalLocationMaster.ID, GETDATE(), 'UPDATED', SUSER_NAME(), 'FunctionalLocationOrig', d.FunctionalLocationOrig, i.FunctionalLocationOrig FROM TblToFunctionalLocationMaster join deleted d on TblToFunctionalLocationMaster.ID = d.ID join inserted i on TblToFunctionalLocationMaster.ID = i.ID
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. And looking at what the code is actually doing, a reference to the TblToFuncionalLocationMaster table is actually not necessary.
1 Like 1 ·
Ha you're right! I had just de-dynamic-ed the SQL.
0 Likes 0 ·
Ok, it works without the need to reference the master table and without the need to use top and rbar form but simply using the function UPDATE for each field of interest. Thank you very much.
0 Likes 0 ·
So, if you can, please vote up @kev riley's answer and mark it by clicking on the thumbs up.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.