Sql trigger to save changes to an audit trail table

I started on an ADS sql table trigger to store changes done on one particular table. Here is the idea:

//-------- sql trigger to store changes on patients table to auditLog Table
//----------------------------------------------------------------------
declare cChanges Char( 5000 );
declare allColumns Cursor ;
declare FieldName Char( 25 );
declare StrSql  Char( 255 );
declare @new cursor as select * from __new;
declare @old cursor as select * from __old; 
open @old; 
fetch @old;
open @new; 
fetch @new;
Set cChanges = '';
Open AllColumns as Select * from system.columns where parent = 'patients';
while fetch allColumns DO
// Try
   FieldName = allColumns.Name;
   StrSql = 'IF @new.'+FieldName
          + '<> @old.'+FieldName
          +' and @old.'+FieldName + '<> [ ] THEN ' 
                       + 'cChanges = Trim( '+cChanges+' ) + @old.'+FieldName
                                   + ' Changed to ' + '@new.'+fieldname
                                   + ' | '+ 'ENDIF ; ' ;
  开发者_开发百科 Execute Immediate StrSql ;
//    Catch ALL
//    End Try;
End While;
if cChanges <> '' THEN
    Insert Into AuditLog ( TableKey, Patient, [table], [user], creation, Changes ) 
         values( @new.patient, @new.patient, [Patietns], User(), Now(), cChanges ) ;
ENDIF;
CLOSE AllColumns;
//--------------------------

The above trigger code errors with reporting variable cChanges does not exists.

Can someone help?

Reinaldo.

The problem is indeed that you cannot access local variables in the scripted executed immediately. What you can do to get around the problem is to use temporary table:

//-------- sql trigger to store changes on patients table to auditLog Table
//----------------------------------------------------------------------
declare cChanges Char( 5000 );
declare allColumns Cursor ;
declare FieldName Char( 25 );
declare StrSql  Char( 255 );
Set cChanges = '';
Open AllColumns as Select * from system.columns where parent = 'patients';
while fetch allColumns DO
// Try
   FieldName = allColumns.Name;

   StrSql = 'SELECT n.FieldName newVal,'
            + 'o.FieldName oldVal '
            + 'INTO #MyTrigTable '
            + 'FROM __new n, __old o';

   EXECUTE IMMEDIATE strSQL;

   IF ( SELECT oldVal FROM #myTrigTable ) <> '' THEN
      IF ( SELECT newVal FROM #myTrigTable ) <> ( SELECT oldVal FROM #myTrigTable ) THEN
         cChanges = 'Construct_SomeThing_Using_#myTrigTable_or_a_cursorBasedOn#MyTrigTable';
         INSERT INTO AuditLog ( TableKey, Patient, [table], [user], creation, Changes ) 
         SELECT patient, patient, 'Patietns', User(), Now(), cChages FROM __new ;
      END;
   END;
   DROP TABLE #myTrigTable;
//    Catch ALL
//    End Try;
End While;
CLOSE AllColumns;
//--------------------------

I believe the problem has to do with your dynamic SQl attempting to set a value declared in your trigger body.

e.g. Your cChanges = TRIM( statement might be causing the problem since cChanges does not exist that context.

You should use binding variables to accomplish this instead of attempting to set using the = sign.

You can see in their docs they say you cannot access those variables directly by going to

http://devzone.advantagedatabase.com/dz/webhelp/advantage9.1/advantage_sql/sql_psm_script/execute_immediate.htm