Hello Helen,

Thanks for jumping in.

> >Yes. Now, I can confirm [check constraints] work just like triggers.
>
> They don't work just like triggers, since you cannot use a CHECK

constraint

> to change a value or to update or delete rows in other tables.

I meant they work the same *regarding the isolation level*. Like triggers,
but unlike decl. RI, CHECKs use the isolation level of the current tx.

> You can
> *write* a check constraint that reads a value in another table but it's a
> practice I strongly advise against. You create additional inter-table
> dependencies in the metadata of your tables that are not protected by

decl.

> RI. RI triggers are designed for this;

I'm all for triggers. But is it safe here ? (see the end of the post.)
Apparently, Alexander says "no".

>
> >I had and still have a hard time with select-form CHECKs. Somehow I can't
> >use values of the currently modified row in the WHERE clause of my

select.

>
> No, because they are not in context. Triggers provide context variables.

But in the following constraint

ALTER TABLE PERSONNEL ADD CONSTRAINT CHK_PERSONNEL CHECK (start_date <
end_date);

"start_date" and "end_date" are in context. Right ?

I was hoping to use the same in-context variables in a select-form CHECK.
Didn't succeed.

> >Well, I don't care now.
>
> Hmmm...

Sorry if it sounds rude. I meant, since CHECKs behave like triggers
*regarding isolation*, I'll just concentrate on triggers which I know
better.

> > >> 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.
> >
> >Don't know why I only had IB/FB's snapshot isolation in mind and forgot
> >about dirty read.
>
> There is *no* dirty read in Firebird whatsoever.

Sorry, I misexpressed myself. I meant : indeed, the matter may not be a
IB/FB matter only, since other isolation levels than "read committed" (be it
dirty read or whatever else) also exist in other SQL engines.

> >So, let me rephrase my question.
> >
> >If my constraints are so that they can't be expressed thru declarative RI
> >but need triggers. Is there a way to avoid the pb described by Claudio in
> >his paper ? That's to say, can I ensure that other connections to my db
> >won't mess up my data integrity by using a different isolation level than
> >"Read committed".
>
> This sounds like a misinterpretation of what you have read. All of the RI
> behaviour (and check constraints too) occur in the context of the current
> transaction. Once your transaction has reached the point of checking
> constraints and firing triggers, it has your row and its dependent rows
> locked. Other transactions can't interfere with your transaction's work

if

> you set up your transaction to block them (and set up the other
> transactions to be blocked).

I'm confused now ;-). I think I understand the locking rules but when a
constraint is evaluated, the check only *reads* data, so there is no lock
involved. right ? What matters is what the tx sees then. And a "read
committed" tx might not read the same data as a "Snapshot" tx. Therefore
each *might* not evaluate equally the same constraint.
I also understand that is why decl. RI are evaluated against committed data
whatever the current tx' isolation level is.
All this making non-declarative RI not 100% safe on dynamic tables unless
you make sure your tx are "read committed".

Do I get it right ?

> >If not, how comes I often saw non-declarative RI advocated even for
> >staightforward RI ?
>
> You used to see it often preached one Robert Schieck, a lone voice who
> didn't trust RI, for no convincing reason.

Yes, that's one I remember. The article titled something like "PKs & FKs, I
don't use them".

> ..although I recall myself going through
> a phase where I was generating all RI triggers manually from an

application

> I wrote, thinking it was pretty hot stuff.

I never got into troubles with that (regarding integrity) ?

> So, now, I use decl. RI for
> everything except lookup relationships -

I do that too. Following your advises. ;-)

Thanks for help.

--
Jerome