February 16, 2016 at 1:39 pm
I have a trigger that looks like it should handle multiple rows in an update however we are getting an error: Get message would update more than one row.
The trigger is below. Could someone please have a look and tell me what I am missing?
DECLARE @vcTriggerTypeVarchar(20)
SET NOCOUNT ON
SET @vcTriggerType = 'INSERTED'--Default to this
--Check if we are running for Insert or Update
IF EXISTS (SELECT * FROM DELETED)--Updates are basically Deletes and Inserts so they put a record in the deleted table
SET @vcTriggerType = 'Updated'
--Insert the Audit Record
INSERT INTO [Audit].[AdminDataRequirementMessage_AuditTrail] (
[DataRequirementMessageTypeGUID],
[DataRequirementMessageTypeConfigSystemChoiceID],
[DataRequirementMessageSPName],
[DataRequirementMessageText],
[IsActive],
[SortOrder],
[AuditReason],
[AuditUser],
[AuditDate])
SELECT
[DataRequirementMessageTypeGUID],
[DataRequirementMessageTypeConfigSystemChoiceID],
[DataRequirementMessageSPName],
[DataRequirementMessageText],
[IsActive],
[SortOrder],
@vcTriggerType,
suser_sname(),
getdate()
FROMinserted
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 16, 2016 at 1:43 pm
What makes you think that the trigger is the problem? Maybe the update statement itself is the problem.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2016 at 2:08 pm
So you are saying my trigger looks fine for updating several records? Not what I wanted to hear 🙁 It is a purchased app and I put audit tables on each of their tables. It works fine except with this table they are updating many rows at the same time.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 16, 2016 at 2:27 pm
I tried manually updating a record that would result in a single row updated. And then one that results in multiple rows.
Both work fine. I should have done that prior to posting.
Thank you for your help. I guess my trigger is fine.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 16, 2016 at 3:00 pm
In your first post you stated you are getting an error message. It would go a long way to debugging the error if you could share the error message. That is a lot simpler than looking through code to see if maybe something looks off.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2016 at 3:11 pm
The issue seems to be outside my trigger.
And, I did include the text of the error message. Error message is as follows:
would update more than one row.
And yeah... you know.. this does not look like a SQL message. I just googled it and is seems as this is a MYSQL message? Odd.. I am still looking into it.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
February 16, 2016 at 3:25 pm
Jeffery Williams (2/16/2016)
The issue seems to be outside my trigger.And, I did include the text of the error message. Error message is as follows:
would update more than one row.
And yeah... you know.. this does not look like a SQL message. I just googled it and is seems as this is a MYSQL message? Odd.. I am still looking into it.
If that is the actual message then it must be a custom error. "would update more than one row" is NOT an actual error message from any system. It doesn't say anything. Or there is more to the error message that wasn't shared.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 16, 2016 at 3:42 pm
I messaged the person that sent the message to my group. They are usually pretty on point but I am confirming. And yes agreed this is not a MS SQL message.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply