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
UPDATE databaseName.TableName SET MarkUp = 1
Column Name Data Type Allows Nulls
Markup - decimal(18, 10) - Not Allows Nulls
AverageCost - decimal(18, 8) - Allows Nulls
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],'')
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
UPDATE [database].[db].[TableName] SET AverageCost = 1 WHERE ID = 1000
---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
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.
TRUSTED BY