Existing scalar function within a trigger is not found
-
Calling an existing scalar function within a trigger which is not found within the trigger (Msg 4121, Level 16, State 1)
The error is the following:
Msg 4121, Level 16, State 1, Procedure Sales.uTu_Customer, Line 12 [Batch Start Line 44]
Cannot find either column "Hashing" or the user-defined function or aggregate "Hashing.CreateJsonInputForSha256SalesCustomer", or the name is ambiguous.
I want to use this trigger as a template to generate this code for all tables that require to have an audit trail.
The function exists and works fine.
This is the trigger where it cannot find the scalar function which exists.
I cannot seem to see or find the error in the code.
create function [Hashing].[CreateSha256KeyFromJsonInputSalesCustomer]
(
@Pky int
)
returns varbinary(32)
as
begin
declare @ColumnOuputInJSON nvarchar(max) =
(
select d.CustomerID,
d.CountryID,
d.CustomerExpenditureClassificationID,
d.CustomerName,
d.CustomerAddress1,
d.CustomerAddress2,
d.CustomerPostalCode,
d.CustomerTown,
d.IsCustomerReseller,
d.IsCustomerCreditRisk,
d.Note,
d.TransactionNumber,
d.UserAuthorizationID,
d.SysStartTime,
d.SysEndTime
from Sales.Customer as d
where d.CustomerID = @Pky
for json auto, include_null_values
);
return hashbytes('SHA2_256', @ColumnOuputInJSON);
end;ALTER TRIGGER Sales.uTu_Customer ON Sales.Customer
WITH
EXECUTE AS owner AFTER UPDATE
AS
BEGIN
/* ********************************************************************************************************************* */
DECLARE
@NowTimestamp datetime2(7) = sysdatetime(),
@OpenEndedSysEndTime datetime2(7) = '99991231 23:59:59.9999999',
@DBAction char(1) = 'U';
--
insert into Audit.SalesCustomerHistory
(
CustomerID,CountryID,CustomerExpenditureClassificationID,CustomerName,CustomerAddress1,CustomerAddress2,CustomerPostalCode,CustomerTown,IsCustomerReseller,IsCustomerCreditRisk,
Note,TransactionNumber,UserAuthorizationID,SysStartTime,SysEndTime,RowLevelHashKey,PriorRowLevelHashKey,FireAuditTrigger,AuditDateTimeStamp,DBAction -- Standardized (generic) Auditing information
)
select d.CustomerID,
d.CountryID,
d.CustomerExpenditureClassificationID,
d.CustomerName,
d.CustomerAddress1,
d.CustomerAddress2,
d.CustomerPostalCode,
d.CustomerTown,
d.IsCustomerReseller,
d.IsCustomerCreditRisk,
coalesce(d.Note, concat('No Message Transaction Number: ', d.TransactionNumber)),
d.TransactionNumber,
d.UserAuthorizationID,
d.SysStartTime,
@NowTimestamp, -- AS SysEndTime
-- ************************************************************************************************************************************************
--Msg 4121, Level 16, State 1, Procedure uTu_Customer, Line 12 [Batch Start Line 44]
--Cannot find either column "Hashing" or the user-defined function or aggregate "Hashing.CreateJsonInputForSha256SalesCustomer", or the name is ambiguous.
Hashing.CreateJsonInputForSha256SalesCustomer(i.CustomerID), -- create current RowLevelHashKey
-- ************************************************************************************************************************************************
d.RowLevelHashKey, -- Use the prior RowLevelHashKey to create the PriorRowLevelHashKey in the new transaction
d.FireAuditTrigger,
@NowTimestamp,
@DBAction
from deleted as d
inner join Inserted as i
on i.CustomerID = d.CustomerID
and i.TransactionNumber = d.TransactionNumber;
--*************************************************************
-- Manage the Standardized (generic) Auditing information
--*************************************************************
update ut
set ut.RowLevelHashKey =Hashing.CreateJsonInputForSha256SalesCustomer(i.CustomerID),
ut.PriorRowLevelHashKey = d.RowLevelHashKey,
ut.TransactionNumber = d.TransactionNumber + 1,
ut.SysStartTime = @NowTimestamp,
ut.SysEndTime = @OpenEndedSysEndTime,
ut.Note = i.Note
from Sales.Customer as ut --updateTable
inner join Inserted as i
on i.CustomerID = ut.CustomerID
and i.TransactionNumber = ut.TransactionNumber
and i.RowLevelHashKey = ut.RowLevelHashKey
inner join Deleted as d
on i.CustomerID = d.CustomerID
and i.TransactionNumber = d.TransactionNumber
and i.RowLevelHashKey = d.RowLevelHashKey;
return
END- This topic was modified 1 year, 10 months ago by Peter Heller.
-
We will need your help in order to be able to help you, so please help us!
😎
It would be greatly appreciated if you could provide the DDL (create table) script, and sample data as an insert statement.
Are all object references schema qualified?
-
You gave us the DDL for [Hashing].[CreateSha256KeyFromJsonInputSalesCustomer], but the trigger is failing on [Hashing].[CreateJsonInputForSha256SalesCustomer].
- CreateJsonInput implies the output of CreateJsonInputForSha256SalesCustomer is json. It sounds more like the intended input for CreateSha256KeyFromJsonInputSalesCustomer. Are you really trying to insert json in RowLevelHashKey rather than the Sha256Key from CreateSha256KeyFromJsonInputSalesCustomer?
- If so, does [Hashing].[CreateJsonInputForSha256SalesCustomer] exist, & can you provide the definition?
-
create table [Audit].[SalesCustomerHistory](
[SalesCustomerHistoryId] [sdSequenceNumber].[SurrogateKeyNumber] identity(1,1) not null,
[AuditDateTimeStamp] [sdSysTime].[AuditTriggerTimestamp] not null,
[DBAction] [dbo].[DbAction] not null,
[CustomerID] [sdSequenceNumber].[SurrogateKeyNumber] not null,
[CountryID] [sdSequenceNumber].[SurrogateKeyNumber] not null,
[CustomerExpenditureClassificationID] [sdSequenceNumber].[SurrogateKeyNumber] null,
[CustomerName] [sdPersonNameString].[CustomerName] null,
[CustomerAddress1] [sdAddressesString].[AddressString] null,
[CustomerAddress2] [sdAddressesString].[AddressString] null,
[CustomerPostalCode] [sdAddressesString].[PostalCodeString] null,
[CustomerTown] [sdAddressesString].[TownString] null,
[IsCustomerReseller] [sdFlagBit].[FlagFalse] null,
[IsCustomerCreditRisk] [sdFlagBit].[FlagFalse] null,
[Note] [sdLongTextString].[Note] not null,
[TransactionNumber] [sdOrdinalNumber].[TransactionNumber] not null,
[UserAuthorizationID] [sdSequenceNumber].[SurrogateKeyNumber] not null,
[SysStartTime] [sdSysTime].[SysStartTime] not null,
[SysEndTime] [sdSysTime].[SysEndTime] not null,
[RowLevelHashKey] [sdHashKey].[RowLevelHashKey] null,
[PriorRowLevelHashKey] [sdHashKey].[RowLevelHashKey] null,
[FireAuditTrigger] [sdFlagString].[FlagYesNoString] not null
) on [PRIMARY]
go
The fix was [hashing].[CreateSha256KeyFromJsonInputSalesCustomer] not [hashing].[CreateSha256FromJsonInputSalesCustomer]. -
The fix was [hashing].[CreateSha256KeyFromJsonInputSalesCustomer] not [hashing].[CreateSha256FromJsonInputSalesCustomer].
Thanks for your suggestion about changing the naming.
-
This problem has been resolved. Thanks to all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply