Avatar of Marianne VAN WYK
Marianne VAN WYKFlag for South Africa

asked on 

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

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.

Open in new window

 The query that I am executing that has the error being thrown is as follow:
UPDATE databaseName.TableName SET AverageCost = 1

Open in new window


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

Open in new window


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

Open in new window


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],'')

Open in new window


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],'')

Open in new window

Catch Clause
 Begin Catch 
   Raiserror('error in [db].[TableName_audit_update] trigger', 16, 1 ) with log
 End Catch

Open in new window


Additionally: When I execute a record-specific query, no errors are being thrown.
UPDATE [database].[db].[TableName] SET AverageCost = 1 WHERE ID = 1000

Open in new window

---
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],'')

Open in new window

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?


SQLMicrosoft SQL Server* SQLTrigger

Avatar of undefined
Last Comment
ste5an
Avatar of ste5an
ste5an
Flag of Germany image

I would drop the try..catch for testing purpose, to get the real error message.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Marianne VAN WYK

ASKER

Hi Scott,

Thanks so much, This is a solution to my issue.

I just would like to understand why this solution is working, as we are inserting the data into the db.Audit table, that has the data type of string for all columns?
Your q stated UPDATE not INSERT, and not to table "audit".
If a table had only string data types, then you're right, "ISNULL(..., '')" should not cause any error(s) at all.

Back to the original UPDATE, the markup column could never be NULL, that's why it never had the same error.
Avatar of ste5an
ste5an
Flag of Germany image

Drop the try..catch to see the original error, it tells you why it fails.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo