it was an error on variable declaring and catching on a new variable when "inserting" or "deleting"

a nice trigger, really...

hope this is the definitive solution.

sorry for the fuss...

Pablo

--- In firebird-support@yahoogroups.com, "pablosanchezuy" <pablos@...> wrote:
>
> 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
>