At 01:16 PM 28/09/2004 +0000, you wrote:

>Hello,
>Here is my problem:
>I have a table company and a table person. Every company possesses
>several persons but the only one of them is said " reference", i.e
>the person by default of the company. The table person thus contains
>a field CONNUM (foreign key of the table company) and a field PERREF
>which takes the value 0 (no) or 1 (yes) and which defines if the
>person is the reference of the company.
>When I want to create a person, or to modify the value of CONNUM or
>PERREF for a given person, i.e. that I allocate her to another
>company or that I appoint her as referent, I have to modify the rest
>of the table in order to be sure that it does not exist 2 referents
>for the same company.
>I make it by triggers with a select to know if there is already a
>referent and then an update of the table.
>I receive then a message of IBEXPERT saying to me: Too many
>concurent execpetion of the same request
>How can it be done?

As Ann explained, your trigger is setting off a chain reaction. Triggers
operate on any row any time it is subjected to the operation that the
trigger applies to.

One way around this would be to introduce a flag on the Person structure
that gets tested in a variety of ways and set or unset according to
conditions; but the logic of the triggers in such solutions is complex.

The simpler way is to link the Company and Referent in a separate
intersection table.

So, you would have
Company, PK CONNUM

Person, PK PERSNUM, FK CONNUM

then --

create table REFERENT (
CONNUM SOMETYPE NOT NULL,
PERSNUM SOMETYPE,
CONSTRAINT PK_REFERENT PRIMARY KEY(CONNUM),
CONSTRAINT FK_REF_COMP FOREIGN KEY(CONNUM)
REFERENCES COMPANY,
CONSTRAINT FK_REF_PERS FOREIGN KEY (PERSNUM)
REFERENCES PERSON);

Now, your trigger on Person would be something like this:

create trigger BA_PERSON for PERSON
active before insert or update or delete
position 2
as
declare variable person2 sometype;
begin

if (INSERTING OR UPDATING) then
BEGIN
if (new.REFERENT = '1') then
begin
person2 = 0;
select COALESCE(PERSNUM, -99) from REFERENT
where CONNUM = new.CONNUM))
into :person2;
if (person2 = -99 or person2 > 0) then
begin
update REFERENT set PERSNUM = new.PERSNUM;
update PERSON set REFERENT = 0
where PERSNUM = :person2; /* see note [**] below */
end
else
insert into REFERENT values (new.CONNUM, new.PERSNUM);
end
END
else
update REFERENT set PERSNUM = NULL
where CONNUM = old.CONNUM;
end

Just keep in mind that any *other* behaviour in the trigger must be
conditioned so as not to fire when this trigger performs the insert or
update.

[**] It's a desirable design objective to avoid inter-row dependencies in
your tables. The solution suggested here assumes that you need to retain
the ability for the user of your applications to decide whether or not a
Person may be assigned as the referent. In so doing, it fails to remove
that inter-row dependency. If your business rules permit you to remove the
REFERENT column from the Person table and rely on the logic of the
Person-Referent relationship alone, the trigger would be simpler and the
rule would be more robust structurally.

./heLen