If any of the fields you concatenate is null, the result will be null.

<any_value> || null = null

You could use something like "coalesce(old.ENTRYID, 'null')" within any
field in your statement.

Regards,

Fabiano

On Sun, Nov 9, 2008 at 6:51 PM, Adrian Wreyford <wreymed@...>wrote:

> I've got the following trigger, that is supposed to store the value of the
> updated record before 'old.' and the new value 'new.' in a table called
> user$stats.
>
> CREATE OR ALTER TRIGGER USU_ENTRY2 FOR ENTRY2
> ACTIVE AFTER UPDATE POSITION 3
> AS
>
> begin
> insert into USER$STATS values(0,USER,'ENTRY2','UPDATE','NOW',
> old.ENTRYID||'~'||old.PATIENTID||'~'||old.TRANSDATE||'~'||old.TRANSCODE||'~'||old.TRANSCODEID||'~'||old.TRANSMODIFIER||'~'||old.TRANSMODIFIERID||'~'||old.TRANSPRINT||'~'||old.TRANSMEMDEBIT||'~'||old.TRANSMEMCREDIT||'~'||old.TRANSMAIDDEBIT||'~'||old.TRANSMAIDCREDIT||'~'||old.TRANSFISIOID||'~'||old.TRANSSCALE||'~'||old.TRANSICDCODE||'~'||old.TRANSICDLEVEL||'~'||old.TRANSICDLEVELID||'~'||old.TRANSEDI||'~'||old.TRANSFACILITY||'~'||old.TRANSDESCRIPTION||'~'||old.TRANSICDADDCODE||'~'||old.TRANSICDADDLEVEL||'~'||old.TRANSICDADDLEVELID||'~'||old.TRANSMULTIPLYTYPE||'~'||old.TRANSFACTOR||'~'||old.TRANSMULTIPLIER||'~'||old.TRANSCODETABLE||'~'||old.TRANSAMOUNT||'~'||old.TRANSDISC||'~'||old.TRANSDEBIT||'~'||old.TRANSCREDIT||'~'||old.TRANSTOTAL
>
> ||'~'||new.ENTRYID||'~'||new.PATIENTID||'~'||new.TRANSDATE||'~'||new.TRANSCODE||'~'||new.TRANSCODEID||'~'||new.TRANSMODIFIER||'~'||new.TRANSMODIFIERID||'~'||new.TRANSPRINT||'~'||new.TRANSMEMDEBIT||'~'||new.TRANSMEMCREDIT||'~'||new.TRANSMAIDDEBIT||'~'||new.TRANSMAIDCREDIT||'~'||new.TRANSFISIOID||'~'||new.TRANSSCALE||'~'||new.TRANSICDCODE||'~'||new.TRANSICDLEVEL||'~'||new.TRANSICDLEVELID||'~'||new.TRANSEDI||'~'||new.TRANSFACILITY||'~'||new.TRANSDESCRIPTION||'~'||new.TRANSICDADDCODE||'~'||new.TRANSICDADDLEVEL||'~'||new.TRANSICDADDLEVELID||'~'||new.TRANSMULTIPLYTYPE||'~'||new.TRANSFACTOR||'~'||new.TRANSMULTIPLIER||'~'||new.TRANSCODETABLE||'~'||new.TRANSAMOUNT||'~'||new.TRANSDISC||'~'||new.TRANSDEBIT||'~'||new.TRANSCREDIT||'~'||new.TRANSTOTAL);
>
> end
>
> Metadata for USER$STATS:
> CREATE TABLE USER$STATS (
> USER_ID Integer NOT NULL,
> USER_NAME Varchar(31),
> USER_TABLE Varchar(31),
> USER_ACTION Varchar(8),
> USER_DATE TIMESTAMP,
> USER_RECORD_ID Varchar(500),
> PRIMARY KEY (USER_ID)
> );
>
> CREATE UNIQUE INDEX IDX_USER$STATS_1 ON USER$STATS(USER_ID);
>
> When viewing the result, USER_RECORD_ID is NULL
>
> Any help will be appreciated
>
> Thanks
>
> Adrian
>
>
> [Non-text portions of this message have been removed]
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>


[Non-text portions of this message have been removed]