--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 01:32 AM 14/03/2007, you wrote:
> >I have two tables master, detail. The tables are related using a
> >foreign key with cascaded updatees etc. When a record's primary

key

> >updates in the master table I want to copy the pre-modified data

(old

> >version) to the master table and also its related child records in
> >the detail table so that I create a new version of that record

using

> >triggers. This way I keep the old version of the records and new
> >version of the records in the sae table.
>
> I hope you are saying that you want to be able to keep the

original

> version of the master record and its children and write

the "changed"

> PK into a new master record and create children for it that match

the

> children of the old record....? I'll work on that (hopeful)

assumption....

>
>
> >e.g. before Update
> >Master table
> >PK_MAST DATA_MASTER
> >1 MyData
> >
> >Detail Table
> >KEY_MAST SEQN DATA_DETAIL
> >1 1 My Detail Data
>
> I'll have to suppose that the PK of the detail record is the FK +

a

> sequence number (SEQN)...
>
>
> >After Update
> >PK_MAST DATA_MASTER
> >1 MyOldData
> >2 MyNewData
> >
> >Detail Table
> >KEY_MAST SEQN DATA_DETAIL
> >1 1 My Old Detail Data
> >2 2 My New Data
> >
> >I havn't been able to do this without violating foreign key or
> >primary key. Has anyone got any ideas.
>
> create trigger bu_master for master
> active before update as
> declare variable new_pk integer; -- presumably
> declare variable new_dm varchar(whatever);
> declare variable new_seqn smallint = -1; -- presumably
> declare variable new_detail_data varchar(whatever);
> begin
> if (new.pk_mast <> old.pk_mast) then
> begin
> -- save the new values to variables and revert the existing

record

> new_pk = new.pk_mast;
> new.pk_mast = old.pk_mast;
> new_dm = new.data_master;
> new.data_master = old.data_master;
>
> -- create the new master record
> insert into master (pk_mast, data_master)
> values (:new_pk, :new_dm);
>
> -- now that the new master exists, create the new child records
> for select seqn, data_detail from detail
> where key_mast = old.pk_mast
> into :new_seqn, :new_detail_data do
> begin
> if (new_seqn >=0) then
> begin
> insert into detail (key_mast, seqn, data_detail)
> values (:new_pk, :new_seqn, :new_detail_data);
> new_seqn = -1;
> new_detail_data = null;
> end
> end
> end
> end
>
> Important:
>
> 1. Make sure you don't have any activity in the Insert triggers

of

> either table that will set off untoward behaviour (recursion, etc.)
> 2. The client must commit this work straight away, i.e., don't

leave

> the uncommitted new rows lying around for the user to meddle with.
>
> ./heLen
>

Worked a treat - thank you vey much.