I can think of two ways to implement this consistency check.

The not so good suggestion first:

If you had an index on (PHONE, D_STATE), your before insert trigger
could look something like

if new.state = 0 then
begin
select first 1 phone
from table
where phone = new.phone
and d_state = 0
into :test;

if (test is not null) then exception ENoDontDoThat;
end

It would probably do the job, but as I am sure others will be quick
to point out, you are querying the same table you are inserting which
can be risky. Secondly it may be still possible for 2 simultaneous
transactions to not see the other ones entry into the table.

A second (and better IMO) suggestion would be to have a gatekeeper
type table and implement a rule that said you must update that record
first.

PhoneLockdown (Phone DOUBLE PRECISION (PK));

In the before insert / update triggers

if new.d_state=0 then
begin
insert into PhoneLockdown(Phone) values (new.Phone);
end

This line will fire a primary key violation, or pause until the other
transaction rollsback depending on your transaction no_wait settings.

In your update / insert / delete triggers for the table put the
following code (obviously adjusting new/old for the respective
triggers).

if ((old.d_state=0) and (new.d_state<>0)) then
begin
delete from phonelockdown where phone = :old.phone;
end



Adam







--- In firebird-support@yahoogroups.com, "Edwin A. Epstein, III"
<eepstein@c...> wrote:

> I just ran into a problem due to an error with a single line of

code.

> Pretty gosh darn important line of code. To say the crap hit the

fan is a

> major understatement. I am now trying to put a "safety" into the

database

> to never allow this again. To do this I think I need a check

constraint,

> but I am at a loss on exactly how to implement it.
>
> I have 3 fields in a table:
>
> PHONE DOUBLE PRECISION
> D_STATE INTEGER
> LOG_ID INTEGER
>
> Now there is a primary key on PHONE and LOG_ID together. This

allows me to

> create multiple lists of phone numbers, but to not have more than

one phone

> number in any single list.
>
> every time a phone number is in "use" the d_state is equal to 0.

Any

> D_STATE value other than 0 is not in use.
> A phone number should not be in use more than once at any given

time.

>
> The following situation is okay:
>
> PHONE,D_STATE,LOG_ID
> 7025551212,0,4000
> 7025551212,1,3000
> 7025551212,1,2000
> 7025551212,-3,1000
>
> This situation is NOT:
>
> PHONE,D_STATE,LOG_ID
> 7025551212,0,4001
> 7025551212,0,4000
> 7025551212,1,3000
> 7025551212,1,2000
> 7025551212,-3,1000
>
> Now the line of code that was so important, has effectively

prevented this

> since I have created this database well over a year ago. However,

I now

> want to put the check in to give it an extra level of safety. I

also need

> to do this to assure those above me that I have in fact taken

measures to

> prevent any future occurances (soften the blow, silver lining, take

your

> pick)
>
> I cannot just create a unique index in addition to the PK since as

you can

> see it is possible for the D_STATE to have 2 values of "1". They

both

> indicate the phone number was no longer in use on the list.
>
> I beleive if I could create a constraint that would not allow 2

D_STATE

> fields to share the specific value of "0" while sharing the same

PHONE

> number that would give me my safety.
>
> If this can be done with a check constraint I would sincerely

appreciate

> your help! :)
>
> P.S - Sorry about the novel, but I wanted to fully explain myself

the first

> time :)
>
> Thanks, Ed