At 06:19 AM 13/07/2006, you wrote:

>Hi All
>
>I need, in a trigger, goes throught all "old" and "new" fields
>values for a table.
>
>I have the following :
>
>1. View who return tables names and fields names
>
>CREATE VIEW VIEWDBTABLES(
> TBTBNAME,
> TBFIELDNAME,
> TBFIELDID,
> TBFIELDTYPE,
> TBFIELDLEN,
> TBFIELDDEC)
>AS
>SELECT
> a.RDB$RELATION_NAME /* as TBTBNAME */,
> b.RDB$FIELD_NAME /* as TBFIELDNAME */,
> b.RDB$FIELD_ID /* as TBFIELDID */,
> d.RDB$TYPE_NAME /* as TBFIELDTYPE */,
> c.RDB$FIELD_LENGTH /* as TBFIELDLEN */,
> c.RDB$FIELD_SCALE /* as TBFIELDDEC */
>FROM
> RDB$RELATIONS a
>INNER JOIN
> RDB$RELATION_FIELDS b
>ON
> a.RDB$RELATION_NAME = b.RDB$RELATION_NAME
>INNER JOIN
> RDB$FIELDS c
>ON
> b.RDB$FIELD_SOURCE = c.RDB$FIELD_NAME
>INNER JOIN
> RDB$TYPES d
>ON
> c.RDB$FIELD_TYPE = d.RDB$TYPE
>WHERE
> a.RDB$SYSTEM_FLAG = 0
> AND
> d.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
>;

First thing, don't alias the output fields. View semantics are that
the view's columns are assigned in the specification order of the
columns in the SELECT statement.

>2. In a trigger. I want to do something like
>
> FOR
> SELECT VIEWDBTABLES.TBFIELDNAME FROM VIEWDBTABLES
> WHERE (VIEWDBTABLES.TBTBNAME = 'BANCOS')
> INTO
> :TMPFIELDNAME DO
> BEGIN

> /*here starts my doubt*/
> /* i need to retrieve all old and new values for all fields in
> the record */
> IF "OLD.TBNAME" <> "NEW.TBNAME" THEN ...

These double quotes are not valid syntax.

The "old" and "new" values pertain to the fields in the table that
owns the trigger, not to any set that you invoke inside the
trigger. (One hopes that you are not attempting to write an
updatable trigger for this particular view !!! i.e. slitting open
your abdomen and proceeding to eat your intestines....)

So, supposing your trigger is an update trigger on a table called
NOT_VIEWTABLES --

For the test above, NOT_VIEWTABLES would have to have a column named TBNAME.

"Old" variables are valid in Before/After Update and Before/After
Delete triggers.
"New" variables are valid in Before/After Insert and Before/After
Update triggers.
They are valid for the

I'm struggling to guess at what you want to check here, but the
following is a valid check for an update trigger on NOT_VIEWTABLES,
assuming TBNAME is a column in that table:

IF (old.TBNAME <> tmpfieldname) THEN...

Also, when doing equality checks on character values from the system
tables, be aware that these data are CHAR() types, not VARCHAR(). A
test such as

WHERE (VIEWDBTABLES.TBTBNAME = 'BANCOS')

won't return any results. You would need to introduce CASTs
somewhere to make a valid comparison....

./heLen