Hi,
I create a trigger with a temporary table.
It raises an error.
Error 5177: Trigger execution failed..... Error 7008: The specified table...
was unable to be opened. Table name: #tmpVALUE

What I want is a general audit information.
All modification put in ONE table (AUDITLOG) as two memo field OldValue and
NewValue.

OldValue NewValue
CUSTNM: AAAA CUSTNM: BBBB

Regards
Saras Setiawan

=====================================================================
The trigger like this

DECLARE tmpCURSOR CURSOR;

DECLARE oldValue STRING;
DECLARE newValue STRING;

//////////////////////////////////////////////////////////////////////////////
oldValue = '';
newValue = '';
OPEN tmpCursor AS SELECT oldVALUE('TABLE1') AS OLDVALUE,
newVALUE('TABLE1') AS NEWVALUE FROM system.iota;
TRY
FETCH tmpCURSOR;
oldVALUE = tmpCursor.OLDVALUE;
newVALUE = tmpCursor.NEWVALUE;
FINALLY
CLOSE tmpCURSOR;
END;

INSERT INTO AUDITLOG
( ComputerName,
UserName,
UserDictionary,
ApplicationID,
TableName,
EventType,
DateTime,
_OldValue,
_NewValue )
VALUES
( myComputer,
myLogin,
User(),
ApplicationID(),
'TABLE1',
'UPDATE',
Now(),
oldValue,
newValue );

=====================================================================
The function NewValue like this

DECLARE dbCURSOR CURSOR;
DECLARE tmpCURSOR CURSOR;

DECLARE stmt STRING;
DECLARE newValue STRING;

newValue = '';
OPEN dbCursor AS SELECT * FROM system.columns WHERE PARENT = TRIM(strName);
TRY
WHILE FETCH dbCURSOR DO
TRY
DROP TABLE __output;
CATCH ALL
END;
stmt = 'DECLARE c CURSOR;'+CHAR(13)+CHAR(10)+
'OPEN c AS SELECT o.['+TRIM(dbCursor.NAME)+'] AS OLDVALUE,
n.['+TRIM(dbCursor.NAME)+'] AS NEWVALUE FROM __OLD o, __NEW
n;'+CHAR(13)+CHAR(10)+
'FETCH c;'+CHAR(13)+CHAR(10)+
'SELECT c.OLDVALUE AS OLDVALUE, c.NEWVALUE AS NEWVALUE INTO __output FROM
system.iota;'+CHAR(13)+CHAR(10)+
'CLOSE c;';
EXECUTE IMMEDIATE stmt;
OPEN tmpCURSOR AS SELECT * FROM __output;
TRY
FETCH tmpCURSOR;
IF tmpCURSOR.OLDVALUE <> tmpCURSOR.NEWVALUE THEN
newValue = newValue + TRIM(dbCursor.NAME)+': '+IIF(tmpCURSOR.NEWVALUE IS
NULL,'',CONVERT(tmpCURSOR.NEWVALUE,SQL_CHAR))+CHAR(13)+CHAR(10);
END;
FINALLY
CLOSE tmpCURSOR;
END;
END;
FINALLY
CLOSE dbCURSOR;
END;
RETURN newValue;