troubleshooting Question

Why is SQL Server 2017 Update Trigger throwing a user-defined error, when I execute a specific query, but not for a similar query?

Avatar of LIBRALEX
LIBRALEXFlag for South Africa asked on
SQLMicrosoft SQL Server* SQLTrigger
6 Comments1 Solution20 ViewsLast Modified:
I have a very strange problem. Executing a specific update query results in a user-defined error being raised.
Msg 50000, Level 16, State 1, Procedure Tablename_Audit_Update, Line 410 [Batch Start Line 0]
error in [db].[TableName_audit_update] trigger
Msg 3616, Level 16, State 1, Line 1
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
 The query that I am executing that has the error being thrown is as follow:
UPDATE databaseName.TableName SET AverageCost = 1

However using a similar query, with a different column name, does not have any errors being raised.
UPDATE databaseName.TableName SET MarkUp = 1

Additional Information
Design:
Column Name   Data Type         Allows Nulls
Markup      - decimal(18, 10) - Not Allows Nulls
AverageCost - decimal(18, 8)  - Allows Nulls

Snippet - AverageCost check within the TableName_Audit_Update trigger.
(Raising Errors)
 IF UPDATE([AverageCost])
   INSERT db.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue)
   SELECT  @AuditTime, suser_sname(), APP_NAME(), Host_Name(), 'db.TableName', 'u', Inserted.[ID],
        NULL,     -- Row Description (e.g. Order Number)
        NULL,     -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
        '[AverageCost]', Cast(Deleted.[AverageCost] as VARCHAR(50)),  Cast(Inserted.[AverageCost] as VARCHAR(50))
          FROM Inserted
             JOIN Deleted
               ON Inserted.[ID] = Deleted.[ID]
               AND isnull(Inserted.[AverageCost],'') <> isnull(Deleted.[AverageCost],'')

Snippet - MarkUp check within the tablename_Audit_Update trigger.
(Working)
 IF UPDATE([MarkUp])
   INSERT db.Audit (AuditDate, SysUser, Application, HostName, TableName, Operation, PrimaryKey, RowDescription, SecondaryRow, ColumnName, OldValue, NewValue)
   SELECT  @AuditTime, suser_sname(), APP_NAME(), Host_Name(), 'db.TableName', 'u', Inserted.[ID],
        NULL,     -- Row Description (e.g. Order Number)
        NULL,     -- Secondary Row Value (e.g. Oder Number for an Order Detail Line)
        '[MarkUp]', Cast(Deleted.[MarkUp] as VARCHAR(50)),  Cast(Inserted.[MarkUp] as VARCHAR(50))
          FROM Inserted
             JOIN Deleted
               ON Inserted.[ID] = Deleted.[ID]
               AND isnull(Inserted.[MarkUp],'') <> isnull(Deleted.[MarkUp],'')
Catch Clause
 Begin Catch 
   Raiserror('error in [db].[TableName_audit_update] trigger', 16, 1 ) with log
 End Catch

Additionally: When I execute a record-specific query, no errors are being thrown.
UPDATE [database].[db].[TableName] SET AverageCost = 1 WHERE ID = 1000
---
1. I am not sure what I am missing? Or how to approach this issue any further.
2. I have also noticed by removing
AND isnull(Inserted.[AverageCost],'') <> isnull(Deleted.[AverageCost],'')
from the trigger for the AverageCost check, the above-mentioned query that results in error then executes without any errors and I am not sure why?


ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros