--- In firebird-support@yahoogroups.com, "TechVale - Gustavo"
<g.c.bianconi@...> 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'
> ;
>
>
> 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 ...
>

What table/view is your trigger for?

OLD an NEW show the values of the current record that is being
changed. You can look at the OLD or NEW values for any field in the
current record, you do not need to select them. Obviously you can't
look at the OLD value in an insert trigger or the NEW value in a
delete trigger. In a BEFORE INSERT or BEFORE UPDATE trigger, you can
modify the NEW value. In AFTER INSERT or AFTER UPDATE triggers, the
NEW value is read only.

It is not clear your for select is meant to be doing in your example.

Firebird does not support field level triggers, the trigger will fire
when the row is changed even if the only fields changed you do not
care about. It is up to you to use IF statements to ignore these changes.

The only potentially confusing thing is NULL handling, because NULL =
NULL returns NULL, so if both the old and new tbname fields are null,
you will not enter the following if statement.

IF (OLD.TBNAME = NEW.TBNAME) THEN
BEGIN

END

Adam