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.
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 )