At 12:03 PM 24/02/2006, you wrote:

>Hello all
>
>I have four tables:
>LOGIT_WLO (id integer, logit_status integer ...)
>LOGIT_ORDER (id integer, logit_wlo_id integer -> references LOGIT_WLO ...)
>LOGIT_REEL (id integer, reel_code integer, srv char(3), logit_order_id ->
>references LOGIT_ORDER ...)
>LOGIT_SCR_ITEM (id, reel_code integer, srv char(3) [both fields reference
>LOGIT_REEL], status ...)
>
>due some computations field 'logit_status' in table LOGIT_WLO
>changes its value.
>
>Is it possible to change the 'status' field in LOGIT_SCR_ITEM in single update
>statement called from after_update trigger of LOGIT_WLO table like :
>
>UPDATE LOGIT_SCR_ITEM SCR
>SET SCR.STATUS = 1
>WHERE ??????
>
>it would be simple when only one field from LOGIT_SCR_ITEM table references
>LOGIT_REEL table, but what is the condition when there are more than one field
>like in the example above.

You *can* do it this way:

create trigger AU_LOGIT_WLO for LOGIT_WLO
active after update
as
begin

if (new.logit_status <> old.logit_status) then
begin
update logit_scr_item SCR
set SCR.status = 1
where exists (
select 1 from LOGIT_ORDER LO
join LOGIT_REEL LR
on LR.LOGIT_ORDER_ID = LO.id
where
LO.LOGIT_WLO = new.id
and SCR.reel_code = LR.reel_code
and SCR.srv = LR.srv)
end
end

But it will be slower than this, I think:

create trigger AU_LOGIT_WLO for LOGIT_WLO
active after update
as
declare variable var1 integer = -99;
declare variable var2 char(3) = '---';
begin

if (new.logit_status <> old.logit_status) then
begin
for select LR.reel_code, LR.srv
from LOGIT_REEL LR
join LOGIT_ORDER LO
on LR.LOGIT_ORDER_ID = LO.ID
where LO.LOGIT_WLO = new.id
into :var1, :var2
do
begin
if (var1 <> -99 and var2 <> '---') then
update logit_scr_item
set status = 1
where reel_code = :var1 and srv = :var2;
var1 = -99;
var2 = '---';
end
end
end

It is rather clumsy to make the foreign key between the two columns
in two tables, instead of using the primary key of the parent table
(LOGIT_REEL) to surrogate the unique combination (reel_code, srv),
but perhaps you have some (unstated) reason to do that..

./heLen