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