hi, I have a table T1 which it has a "status history" linked table, such like this :

create table T1 (
id integer ,
status char(1)
constraint T1pk primary key id)

create table T2 (
id integer,
last_status char(1),
old_status char(1),
last_date date
constraint T2pk primary key (id,last_status),
foreign key (id) references T1 (id)
)

On T2 there are a lot of status changes, there is, inserts and deletes (no updates) .

For every T2 insert there is a trigger :

update T1 A set A.status = new.last_status
where A.id = new.id;

What i cannot manage to build (make it compile)is the after delete trigger on T2 .
Should be something like :


select first 1 A.last_status
from T2 A where A.id = old.id and A.last_status <> old.last_status
order by last_date desc
(this gives me the last known status, or empty)

followed by the update of T1 :

update T1 B set B.status = A.last_status where B.id = A.id


I guess it's a syntax problem. I may brake this into a sp and the udpate, but it'd be better in one go into the trigger.

Regards

Pablo