sqltriggerssybase

"Column 'PK1' not found" at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery()


Presently, I am working with this trigger:

CREATE TRIGGER "TrigUploadEmailInspectionInsert" AFTER INSERT
ORDER 1 ON "EmailInspectionQuery"
REFERENCING OLD AS initial NEW AS updated 
FOR EACH ROW
BEGIN
    DECLARE count int;
    IF f_are_triggers_enabled() = 1 THEN
      SELECT Count(PK1) INTO count FROM TransactionUpload
      WHERE PK1 =  updated.ID AND TYPE = 'EMAIL_QUERY';
      CASE
        WHEN count = 0 THEN
         INSERT INTO TransactionUpload (PK1, PK2, TYPE, ACTION, ACTION_SEQ, UPD_TMSTP, UI_SAVE_STATUS) 
     VALUES ( PK1, 'N/A', 'EMAIL_QUERY', 'I', 0, CURRENT TIMESTAMP, NULL )
      END CASE;  
    END IF;
END

Here are the table details:

TransactionUpload
TYPE,12,nvarchar(12),N
PK1,255,nvarchar(255),N
PK2,12,nvarchar(12),N
ACTION_SEQ,4,integer,N
ACTION,1,nvarchar(1),N
MOBILE_STATUS,10,nvarchar(10),Y
MOBILE_STATUS_SET_INACT,1,nvarchar(1),Y
UPD_TMSTP,8,timestamp,Y
SYNC_SAVE_STATUS,1,char(1),Y
UI_SAVE_STATUS,1,char(1),Y
ASYNC_PROCESSING,1,char(1),Y
MYSAPSSO2,1024,char(1024),Y

EmailInspectionQuery
column_name,width,base_type_str,nulls
ID,12,nvarchar(12),N
CR_DATE,8,timestamp,Y
ERNAME,12,nvarchar(12),Y
AUFNR,12,nvarchar(12),Y
INSP_DUE,8,timestamp,Y
INSP_STATUS,20,nvarchar(20),Y
EQUNR,18,nvarchar(18),Y
HEQUIPMENT,128,nvarchar(128),Y
HOPEN_RECOMMENDATIONS,1,char(1),Y
HTERRITORY,30,nvarchar(30),Y

When I run the C#/.NET source code that involves inserting data into EmailInspectionQuery I get this error, but it doesn't make any sense to me because TransactionUpload clearly has a PK1 column. Here is the exception:

ex.InnerException.InnerException {"Column 'PK1' not found"}
    Data: {System.Collections.ListDictionaryInternal}
    ErrorCode: -2147467259
    Errors: {iAnywhere.Data.SQLAnywhere.SAErrorCollection}
    HResult: -2147467259
    HelpLink: null
    InnerException: null
    Message: "Column 'PK1' not found"
    NativeError: -143
    Source: "SQL Anywhere .NET Data Provider"
    StackTrace: "   at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteNonQuery()\r\n   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\r\n at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown
---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.<UpdateAsync>d__0.MoveNext()"
    TargetSite: {Int32 ExecuteNonQuery()}

Does anyone have any suggestions? TIA.


Solution

  • Found the problem, needed to change this line:

    INSERT INTO TransactionUpload (PK1, PK2, TYPE, ACTION, ACTION_SEQ, UPD_TMSTP, UI_SAVE_STATUS) 
         VALUES ( PK1, 'N/A', 'EMAIL_QUERY', 'I', 0, CURRENT TIMESTAMP, NULL )
    

    To,

    INSERT INTO TransactionUpload (ID, PK2, TYPE, ACTION, ACTION_SEQ, UPD_TMSTP, UI_SAVE_STATUS) 
         VALUES ( updated.ID, 'N/A', 'EMAIL_QUERY', 'I', 0, CURRENT TIMESTAMP, NULL )