The RDB$VALID_BLR field

The field RDB$VALID_BLR was added to the system tables RDB$PROCEDURES and RDB$TRIGGERS in Firebird 2.1. Its purpose is to signal possible invalidation of a PSQL module when a domain or a table column upon which the module depends is altered. If such invalidations occur, RDB$VALID_BLR is set to 0 for any procedure or trigger whose code is no longer valid.

The following query will find the modules that depend on a specific domain and report the state of their RDB$VALID_BLR fields:

select * from (
  select 'Procedure', rdb$procedure_name, rdb$valid_blr from rdb$procedures
    union
  select 'Trigger', rdb$trigger_name, rdb$valid_blr from rdb$triggers
) (type, name, valid)
where exists
  (select * from rdb$dependencies
   where rdb$dependent_name = name and rdb$depended_on_name = 'MYDOMAIN')

/* Replace MYDOMAIN with the actual domain name. Use all-caps if the domain
   was created case-insensitively. Otherwise, use the exact capitalisation. */

The following query will find the modules that depend on a specific table column and report the state of their RDB$VALID_BLR fields:

select * from (
  select 'Procedure', rdb$procedure_name, rdb$valid_blr from rdb$procedures
    union
  select 'Trigger', rdb$trigger_name, rdb$valid_blr from rdb$triggers
) (type, name, valid)
where exists
  (select * from rdb$dependencies
   where rdb$dependent_name = name
   and rdb$depended_on_name = 'MYTABLE' and rdb$field_name = 'MYCOLUMN')

/* Replace MYTABLE and MYCOLUMN with the actual table and column names.
   Use all-caps if the table/column was created case-insensitively.
   Otherwise, use the exact capitalisation. */

Unfortunately, not all PSQL invalidations will be reflected in the RDB$VALID_BLR field. After changing a domain or table column, it is therefore advisable to have a good look at all the procedures and triggers reported by the above queries, even those having a 1 in the “VALID” column.

Please notice that for PSQL modules inherited from earlier Firebird versions (including a number of system triggers, even if the database was created under Firebird 2.1 or higher), RDB$VALID_BLR is NULL. This does not imply that their BLR is invalid.

The isql commands SHOW PROCEDURES and SHOW TRIGGERS flag modules whose RDB$VALID_BLR field is zero with an asterisk. SHOW PROCEDURE PROCNAME and SHOW TRIGGER TRIGNAME, which display individual PSQL modules, do not signal invalid BLR.