I've written an after update trigger for the change log on one table. It works but I can't help thinking there must be a better way (even if just avoiding so much text). Any suggestions

BEGIN
DECLARE ShortName VARCHAR;
DECLARE LongName VARCHAR;
DECLARE LogLevel VARCHAR;
DECLARE Logging SENSITIVE CURSOR FOR LogTbl;
DECLARE Naming SENSITIVE CURSOR FOR Names;
DECLARE Config SENSITIVE CURSOR FOR ConfigParams;

PREPARE ConfigParams FROM 'SELECT CAST(_ParamData AS VARCHAR(5) ) AS Level FROM Config WHERE _ID = ''alCompanies''';

OPEN Config;
FETCH FROM Config(Level) INTO LogLevel;

IF LogLevel <> 'None' THEN
PREPARE LogTbl FROM 'SELECT * FROM TfRLog';
PREPARE Names FROM 'SELECT Name AS ShortName, CAST(Description AS VARCHAR(30)) AS LongName FROM Configuration.Users WHERE Name = ?';

OPEN Naming USING CURRENT_USER;
FETCH FROM Naming(ShortName) INTO ShortName;
FETCH FROM Naming(LongName) INTO LongName;

OPEN Logging;

IF LogLevel = 'Field' THEN
 IF NEWROW._Name <> OLDROW._Name THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Name',OLDROW._Name,NEWROW._Name);
 END IF;
 IF NEWROW._fkCompanies_Parent <> OLDROW._fkCompanies_Parent THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkCompanies_Parent',OLDROW._fkCompanies_Parent,NEWROW._fkCompanies_Parent);
 END IF;
 IF NEWROW._Website <> OLDROW._Website THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Website',OLDROW._Website,NEWROW._Website);
 END IF;
 IF NEWROW._fkOrgType <> OLDROW._fkOrgType THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkOrgType',OLDROW._fkOrgType,NEWROW._fkOrgType);
 END IF;
 IF NEWROW._fkSICGroup <> OLDROW._fkSICGroup THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkSICGroup',OLDROW._fkSICGroup,NEWROW._fkSICGroup);
 END IF;
IF NEWROW._fkSICCode <> OLDROW._fkSICCode THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkSICCode',OLDROW._fkSICCode,NEWROW._fkSICCode);
 END IF;
IF NEWROW._eMailDomain <> OLDROW._eMailDomain THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_eMailDomain',OLDROW._eMailDomain,NEWROW._eMailDomain);
 END IF;
IF NEWROW._RSSCheck <> OLDROW._RSSCheck THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_RSSCheck ',OLDROW._RSSCheck ,NEWROW._RSSCheck );
 END IF;
IF NEWROW._fkMarkets <> OLDROW._fkMarkets THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkMarkets',OLDROW._fkMarkets,NEWROW._fkMarkets);
 END IF;
IF NEWROW._fkSandT_Turnover <> OLDROW._fkSandT_Turnover THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkSandT_Turnover',OLDROW._fkSandT_Turnover,NEWROW._fkSandT_Turnover);
 END IF;
IF NEWROW._fkSandT_Staffing <> OLDROW._fkSandT_Staffing THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_fkSandT_Staffing',OLDROW._fkSandT_Staffing,NEWROW._fkSandT_Staffing);
 END IF;
IF NEWROW._YearEnd <> OLDROW._YearEnd THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_YearEnd',OLDROW._YearEnd,NEWROW._YearEnd);
 END IF;
IF NEWROW._Status <> OLDROW._Status THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Status',OLDROW._Status,NEWROW._Status);
 END IF;
IF NEWROW._Source <> OLDROW._Source THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Source',OLDROW._Source,NEWROW._Source);
 END IF;
IF NEWROW._Notes <> OLDROW._Notes THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Notes',OLDROW._Notes,NEWROW._Notes);
 END IF;
IF NEWROW._Links <> OLDROW._Links THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Links',OLDROW._Links,NEWROW._Links);
 END IF;
IF NEWROW._Profile <> OLDROW._Profile THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Profile ',OLDROW._Profile ,NEWROW._Profile );
 END IF;
IF NEWROW._Products <> OLDROW._Products THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_Products',OLDROW._Products,NEWROW._Products);
 END IF;
IF NEWROW._UserFlags <> OLDROW._UserFlags THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_UserFlags',OLDROW._UserFlags,NEWROW._UserFlags);
 END IF;
IF NEWROW._URLList <> OLDROW._URLList THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_URLList',OLDROW._URLList,NEWROW._URLList);
 END IF;
IF NEWROW._MarketPlace <> OLDROW._MarketPlace THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name,'_MarketPlace',OLDROW._MarketPlace,NEWROW._MarketPlace);
 END IF;

ELSEIF  LogLevel = 'Table' THEN
  INSERT INTO Logging(_Table,  _Type, _Level, _TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo)
  VALUES('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName, LongName,NEWROW._ID,NEWROW._Name);

END IF;

END IF;

END


Roy Lambert

Roy

> I've written an after update trigger for the change log on one table. It
works but I can't help thinking there must be a better way (even if just
avoiding so much text). Any suggestions

Probably a bit less efficient (but definitely less typing!) how about using
a parameterised stored proc called from the trigger eg

BEGIN
 DECLARE query Statement;
 IF _Before <> _After THEN
   PREPARE QUERY FROM 'INSERT INTO Logging(_Table, _Type, _Level,
_TimeStamp, _fkStaff, _UserName, _Key1, _NameInfo, _Field, _Before, _After)
VALUES(?,?,?,CURRENT_TIMESTAMP,?,?,?,?,?,?,?)';
   EXECUTE QUERY USING  _Table, _Type, _Level, _fkStaff, _UserName, _Key1,
_NameInfo, _Field, _Before, _After;
 END IF;
END

and (assuming _before and _after fields are clob) call it with

CALL LogChange('Companies','Change',LogLevel,CURRENT_TIMESTAMP, ShortName,
LongName,NEWROW._ID,NEWROW._Name,'_Name',CAST(OLDROW._Name AS
CLOB),CAST(NEWROW._Name AS CLOB));


John

John


I thought of that, but wasn't sure the text reduction vs cut'n'paste with a bit of edit would be worth any performance loss. I guess I'm spoilt with Delphi. I started thinking of using a procedure, passing in a table simply looping round the Fields. About 20 lines in all. I'll have to wait for Tim to allow us to loop round NEWROW and OLDROW I guess.

I could do it as an external procedure but again there'd be a performance hit (I think).

Roy Lambert

Roy,

<< I thought of that, but wasn't sure the text reduction vs cut'n'paste with
a bit of edit would be worth any performance loss. I guess I'm spoilt with
Delphi. I started thinking of using a procedure, passing in a table simply
looping round the Fields. About 20 lines in all. I'll have to wait for Tim
to allow us to loop round NEWROW and OLDROW I guess. >>

That stuff will be coming with the array (done) and row (almost done)
support.  I'll still need to add a way to reference columns dynamically, but
that should be doable.

--
Tim Young
Elevate Software
www.elevatesoft.com