Advantage Developer Zone

 
 
 

Leverage the Power of Triggers in Version 7.0 to "Bullet-Proof" Your Referential Integrity

Friday, July 14, 2006

Enjoy the power of Advantage

Traditionally, an AutoInc field or a manually incremented field has been used to insure uniqueness of primary key field values. The only problem is that conflicts can occur when importing data from other tables.

An AutoGUID trigger will protect against this problem by inserting a GUID in a specified field (the first field in this example) whenever a record is inserted.

NOTE: When using a trigger of this nature, the record insert that is replaced by the INSTEAD OF trigger will still show in a grid like a ghost record. To prevent this problem, add a refresh after the post of dataset objects which are attached to tables which have an INSTEAD OF trigger associated with them. Advantage Data Architect(ARC) does not have this included in it's code (because not all tables are associated with INSTEAD OF triggers), so testing this trigger with ARC will show a ghost record until a refresh is done.

GUID defined: (pronounced goo-id) A Globally Unique Identifier (GUID) that can be used across all computers and networks wherever a unique identifier is required.

An Advantage trigger can be an SQL script, a Windows DLL, Linux Shared Object, COM Object, or a .Net Assembly. The example below shows how to register a Windows DLL as a trigger. This would be an INSTEAD OF trigger type on the INSERT event of the table object in the data dictionary:

 
The sample code below can be used to write a Delphi DLL which can referenced in the trigger interface of a table object of an Advantage Data Dictionary (with version 7.0 or newer).
 
//--------------------Beginning of Code Sample--------------//

library AutoGUID;
{$INCLUDE versions.inc}
{$IFDEF ADSDELPHI7_OR_NEWER}  
    {$WARN UNSAFE_TYPE OFF}  
    {$WARN UNSAFE_CODE OFF}  
    {$WARN UNSAFE_CAST OFF}
{$ENDIF}
uses  SysUtils,  Classes,  ace,  adscnnct,  adsset,  adsdata,  adstable,  COMobj;
// Utility Function
Prototypeprocedure SetError ( conn : TAdsConnection; code : UNSIGNED32; err  : string ); forward;
// Sample Advantage Trigger function. If you change the name of this
// function, remember to also change the name in the exports list at the bottom
// of this file.
function InsertGUID( 
    ulConnectionID : UNSIGNED32;
    // (I) Unique ID identifying the user causing this trig 
    hConnection    : ADSHANDLE; 
    // (I) Active ACE connection handle user can perform                              
    //     operations on 
    pcTriggerName  : PChar;     
    // (I) Name of the trigger object in the dictionary 
    pcTableName    : PChar;     
    // (I) Name of the base table that caused the trigger 
    ulEventType    : UNSIGNED32;
    // (I) Flag with event type (insert, update, etc.)  \
    ulTriggerType  : UNSIGNED32;
    // (I) Flag with trigger type (before, after, etc.) 
    ulRecNo        : UNSIGNED32 
    // (I) Record number of the record being modified
    ) : UNSIGNED32;
    {$IFDEF WIN32}
        stdcall;
    {$ENDIF}
    {$IFDEF LINUX}
        cdecl;
    {$ENDIF}
    // Do not change the prototype.const 
    // In this case, the first field is the Primary Key field 
    //   in the base table that needs the AutoGUID value. 
    // This constant definition is necessary because 
    //   triggers don't take parameters. 
    iGUIDfieldNum