In my work with the employee punching I needed to be able to answer a request on changed employees. In NAV 2009 R2 we got the CRM Connector and that included database triggers that are executed when the data is modified. Previously we only had global triggers that where executed when the table triggers where executed. That meant that a C/AL INSERT, MODIFY, DELETE or RENAME did not fire a global trigger but they do fire the database triggers.
The database trigger handling is based on single instance codeunit 5150, Integration Management. Microsoft just released a patch for this codeunit.
What I did is to create another single instance codeunit as an add in for this codeunit. Added my codeunit as a global variable to codeunit 5150 and made a few additions to the code.
[code]GetDatabaseTableTriggerSetup(TableID : Integer;VAR Insert : Boolean;VAR Modify : Boolean;VAR Delete : Boolean;VAR Rename : Boolean)
IF COMPANYNAME = ” THEN
EXIT;
//#PU-
IF PunchIntegrationAddin.GetConnectorEnabled(TableID,Insert,Modify,Delete,Rename) THEN
EXIT;
//#PU+
IF NOT MarketingSetupRead THEN BEGIN
MarketingSetupRead := TRUE;
IF MarketingSetup.GET THEN
IntegrationActivated := MarketingSetup."Enable Connector";
END;
IF NOT IntegrationActivated THEN
EXIT;
IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;
OnDatabaseInsert(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseInsert(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
OnDatabaseModify(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseModify(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
OnDatabaseDelete(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseDelete(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
// Handle exceptions where "Deleted On" should not be set.
IF RecRef.NUMBER = DATABASE::"Sales Header" THEN BEGIN
FieldRef1 := RecRef.FIELD(58); // Invoice
SkipDeletion := FieldRef1.VALUE;
END;
IF NOT SkipDeletion THEN
IntegrationRecord."Deleted On" := TimeStamp;
CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;
OnDatabaseRename(RecRef : RecordRef;XRecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseRename(RecRef,XRecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);[/code]
I also made a copy of table 5151, Integration Record, just for my employee changes. This is the code in my single instance integration add in codeunit.
[code]OBJECT Codeunit 10001037 Punch Integration Addin
{
OBJECT-PROPERTIES
{
Date=27.06.12;
Time=14:39:59;
Modified=Yes;
Version List=PUNCH6.00;
}
PROPERTIES
{
Permissions=TableData 5200=r,
TableData 10001021=rimd,
TableData 10010238=r;
SingleInstance=Yes;
OnRun=BEGIN
END;
}
CODE
{
VAR
PunchSetupRead@1200050000 : Boolean;
IntegrationActivated@1200050001 : Boolean;
Text001@1200050002 : TextConst ‘ENU=Error creating %1 – %2. It already exists.;ISL=Villa kom upp við stofnun %1 – %2. Er þegar til.’;
PROCEDURE GetConnectorEnabled@1200050000(TableID@1200050004 : Integer;VAR Insert@1200050003 : Boolean;VAR Modify@1200050002 : Boolean;VAR Delete@1200050001 : Boolean;VAR Rename@1200050000 : Boolean) : Boolean;
VAR
PunchSetup@1005 : Record 10001049;
BEGIN
IF COMPANYNAME = ” THEN
EXIT;
IF NOT PunchSetupRead THEN BEGIN
PunchSetupRead := TRUE;
IF PunchSetup.GET THEN
IntegrationActivated := PunchSetup."Enable Connector";
END;
IF NOT IntegrationActivated THEN
EXIT(FALSE);
IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;
EXIT(Insert OR Modify OR Delete OR Rename);
END;
PROCEDURE OnDatabaseInsert@2(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;
PROCEDURE OnDatabaseModify@3(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;
PROCEDURE OnDatabaseDelete@4(RecRef@1000 : RecordRef);
VAR
IntegrationRecord@1001 : Record 10001021;
TimeStamp@1003 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Deleted On" := TimeStamp;
CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;
END;
PROCEDURE OnDatabaseRename@5(RecRef@1000 : RecordRef;XRecRef@1001 : RecordRef);
VAR
IntegrationRecord@1003 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
TimeStamp@1002 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IntegrationRecord."Employee No." := FieldRef1.VALUE;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;
PROCEDURE EnableConnector@7();
VAR
WebServiceLog@1200050000 : Record 10001017;
BEGIN
SetupWebService;
SetupIntegrationTables;
END;
PROCEDURE DisableConnector@10();
VAR
PunchSetup@1000 : Record 10001049;
Company@1002 : Record 2000000006;
MultipleCompanies@1003 : Boolean;
BEGIN
MultipleCompanies := FALSE;
Company.SETFILTER(Name,’<>%1′,COMPANYNAME);
IF Company.FINDSET THEN BEGIN
REPEAT
PunchSetup.CHANGECOMPANY(Company.Name);
IF PunchSetup.GET THEN
IF PunchSetup."Enable Connector" THEN
MultipleCompanies := TRUE;
UNTIL (Company.NEXT = 0) OR MultipleCompanies;
END;
IF NOT MultipleCompanies THEN BEGIN
DeleteWebService;
DeleteIntegrationTables;
END;
END;
LOCAL PROCEDURE SetupWebService@11();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
INIT;
"Object Type" := "Object Type"::Codeunit;
"Service Name" := ‘NAVPunch’;
"Object ID" := CODEUNIT::"Punch Web Service Mgt.";
Published := TRUE;
IF NOT INSERT THEN
MESSAGE(Text001,TABLECAPTION,"Service Name");
END;
END;
LOCAL PROCEDURE DeleteWebService@1200050001();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
IF GET("Object Type"::Codeunit,’NAVPunch’) THEN
DELETE;
END;
END;
LOCAL PROCEDURE SetupIntegrationTables@8();
BEGIN
InitializeIntegrationRecords(DATABASE::Employee);
InitializeIntegrationRecords(DATABASE::"Punch Employee Setup");
END;
LOCAL PROCEDURE DeleteIntegrationTables@1200050008();
BEGIN
DeleteIntegrationRecords(DATABASE::Employee);
DeleteIntegrationRecords(DATABASE::"Punch Employee Setup");
END;
LOCAL PROCEDURE InitializeIntegrationRecords@9(TableID@1000 : Integer);
VAR
RecRef@1002 : RecordRef;
BEGIN
WITH RecRef DO BEGIN
OPEN(TableID,FALSE);
IF FINDSET(FALSE) THEN
REPEAT
InsertUpdateIntegrationRecord(RecRef,CURRENTDATETIME);
UNTIL NEXT = 0;
CLOSE;
END;
END;
LOCAL PROCEDURE UpdateParentIntegrationRecord@17(RecRef@1000 : RecordRef;TimeStamp@1008 : DateTime);
VAR
Employee@1200050000 : Record 5200;
FieldRef1@1004 : FieldRef;
ParentRecRef@1006 : RecordRef;
BEGIN
CASE RecRef.NUMBER OF
DATABASE::"Punch Employee Setup":
BEGIN
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IF Employee.GET(FieldRef1.VALUE) THEN BEGIN
ParentRecRef.GETTABLE(Employee);
InsertUpdateIntegrationRecord(ParentRecRef,TimeStamp);
END;
END;
END;
END;
LOCAL PROCEDURE InsertUpdateIntegrationRecord@16(RecRef@1000 : RecordRef;IntegrationLastModified@1001 : DateTime);
VAR
IntegrationRecord@1002 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
BEGIN
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
WITH IntegrationRecord DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
IF FINDFIRST THEN BEGIN
"Modified On" := IntegrationLastModified;
MODIFY;
END ELSE BEGIN
INIT;
"Integration ID" := CREATEGUID;
"Record ID" := RecRef.RECORDID;
"Table ID" := RecRef.NUMBER;
"Modified On" := IntegrationLastModified;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
"Employee No." := FieldRef1.VALUE;
INSERT;
END;
END;
END;
END;
LOCAL PROCEDURE DeleteIntegrationRecords@1200050004(TableID@1200050000 : Integer);
VAR
IntegrationRecord@1200050001 : Record 10001021;
BEGIN
WITH IntegrationRecord DO BEGIN
SETRANGE("Table ID",TableID);
IF NOT ISEMPTY THEN
DELETEALL;
END;
END;
LOCAL PROCEDURE IsIntegrationRecord@6(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::Employee]
THEN
EXIT(TRUE);
EXIT(FALSE);
END;
LOCAL PROCEDURE IsIntegrationRecordChild@18(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::"Punch Employee Setup"]
THEN
EXIT(TRUE);
EXIT(FALSE);
END;
BEGIN
END.
}
}[/code]
This makes it easy for me to find out if the employee has changed since the last synchronization with this function.
[code]EmployeeModified(VAR Employee : Record Employee;LastModified : DateTime) : Boolean
IF LastModified = 0DT THEN EXIT(TRUE);
IF Integration.ISEMPTY THEN
EXIT(Employee."Last Date Modified" >= DT2DATE(LastModified));
RecRef.GETTABLE(Employee);
Integration.SETCURRENTKEY("Record ID");
Integration.SETRANGE("Record ID",RecRef.RECORDID);
IF Integration.FINDFIRST THEN
EXIT(Integration."Modified On" > LastModified)
ELSE
EXIT(FALSE);[/code]