quinta-feira, 20 de maio de 2010

SQL para definir valores default no Firebird.

Define o valor default para campos númericos, e campos varchar e char de tamanho 1 que começarem com FL_ (flag)

update RDB$RELATION_FIELDS set rdb$default_source = 'default 0' where RDB$RELATION_FIELDS.rdb$field_source in (
select
trim(rdb$relation_fields.rdb$field_source)
from rdb$relations
join rdb$relation_fields on rdb$relation_fields .rdb$relation_name = rdb$relations.rdb$relation_name
join rdb$fields on rdb$relation_fields.rdb$field_source = rdb$fields .rdb$field_name
join rdb$types on trim(rdb$types.rdb$type) = trim(rdb$fields.rdb$field_type)
where rdb$types.rdb$type_name in ('FLOAT','DOUBLE','INT64')
and rdb$relation_fields.rdb$field_name not like '%$%'
or ((rdb$types.rdb$type_name in ('VARYING','TEXT')) and (rdb$field_length = 1) and rdb$relation_fields.rdb$field_name like 'FL_%'))