--- In firebird-support@yahoogroups.com, "Jerome Bouvattier" <JBouvattier@I...> wrote:

> Hello,
>
> I am thinking about using "temporal tables" as suggested by R. Snodgrass in
> his book "Developing Time-Oriented Database Applications in SQL".
> Unfortunately, in such a design, constraints like PK or FK cannot be
> achieved thru declarative referential integrity. So I'm left with CHECKs and
> Triggers.

Jerome, fortunately I did'nt read this book :) As you mentioned, Firebird is'nt
constructed to widely use this trick. Two times in my life I implemented this -
one time I was lazy to accurately design functionality to use once a year,
another - had not time to re-design couple of tables to get quickly answer for
unpredicted user's question. Look into direction of selectable stored
procedures and views instead of temporal tables, in Firebird they are intended
to solve problems which it is suitable to solve via temporary tables in MSSQL
and Oracle.

> I know triggers use the same isolation level as the current transaction but
> is it also true for CHECKs contraints ? Or are the latter ran against
> committed data just like declarative RI ?

Can't answer because don't use select-form checks. Think the same as
trigger - internally they are implemented as hidden before trigger which fires
after user-defined ones. Anyway it is easy to check experimentally - create
two simply test tables and try modifications which should violate check in
two connections. Note to emulate FK you should make checks on both
"master" and "detail" to check modifications of each other, unidirectional
check will check modification of current record in time of it's performing.

> Is there a solution in FB 1.5 to ensure that transactions with isolation
> level different from "Read committed" can't modify data in the database ?

No. And no for any other SQL engine.

Best regards,
Alexander.