Share

Advantage Database - How to use triggers to keep table history

This article explains how to use triggers to keep a history of specific changes to a table.
In this example I am using Advantage V9, I have a table which I will call TABLEA and a history table called TABLEA_HISTORY.
TableA_History contains 3 fields that are not in TABLEA.

  • LogTime - Time the change was made.
  • Action - Whether the change was 'Insert', 'Update' or 'Delete'.
  • UserName - Person who has made the change.
The client application all use the same login details to connect to the server.
First is to store when a record is inserted in TableA, below is the trigger script with trigger type set to 'AFTER'.
 
declare @AppID cursor as EXECUTE PROCEDURE sp_GetApplicationID();
open @AppID;
fetch @AppID;
INSERT INTO TABLEA_HISTORY (LogTime, Action, UserName,
Field1, Field2, Field3)
SELECT CURRENT_TIMESTAMP(),'UPDATE',trim(@AppID.ApplicationID),
__new.Field1, __new.Field2, __new.Field3
from __new;
 
Second is to store when a record is update, below is the trigger script with type set to 'AFTER'.
 
declare @AppID cursor as EXECUTE PROCEDURE sp_GetApplicationID();
open @AppID;
fetch @AppID;
INSERT INTO TABLEA_HISTORY (LogTime, Action, UserName,
Field1, Field2, Field3)
SELECT CURRENT_TIMESTAMP(),'UPDATE',trim(@AppID.ApplicationID) ,
a.Field1, a.Field2, a.Field3
from __new a,__old b
where
a.Field1 <> b.Field1
or
a.Field2 <> b.Field2
or
a.Field3 <> b.Field3
 
Third is to store when a record is deleted, below is the trigger script with the type set to 'BEFORE'.
 
declare @AppID cursor as EXECUTE PROCEDURE sp_GetApplicationID();
open @AppID;
fetch @AppID;
INSERT INTO TABLEA_HISTORY (LogTime, Action, UserName,
Field1, Field2, Field3)
SELECT CURRENT_TIMESTAMP(),'DELETE',trim(@AppID.ApplicationID),
__new.Field1, __new.Field2, __new.Field3
trim(@AppID.ApplicationID)
from __old;
 
This will create the history records, but to set the applicationID to a tag I created a stored procedure which would set the applicationID, this would then be called when the user logs into the application.
 
CREATE PROCEDURE SetAppID
(
AppID CHAR ( 40 )
)
BEGIN
DECLARE @AppID Char(40);
@AppID = (Select AppID from __input);
EXECUTE PROCEDURE sp_SetApplicationID(@AppID);
END;
 
Below is an example of how I call the stored procedure (Delphi).
Add a stored procedure component that references the one above.
Then just after the user has logged in call the stored procedure.
 
SetAppIDStoredProc.Params[0].AsString := LogInName;
SetAppIDStoredProc.ExecProc;

 

 

Written 21/02/2011 - Copyright RicRac Developments 2011