Trigger Question
Poster asked whether triggers can mirror rows from table A into an identical backup table A_back whenever A changes. A reply confirmed it works, noting there's no syntax for referencing a whole row, so each column must be listed individually. Sample code was given: insert, update and delete triggers using REFERENCING NEW/OLD ... FOR EACH ROW to insert, update or delete the matching row in A_back (keyed on col1). A follow-up corrected the delete trigger, which had omitted FOR EACH ROW.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Is there anyway for duplicate a record, using triggers, from one table to another table in case of any changes in the first table? Let say I have a table 'A' with two fields and another table, 'A_back' with the exact two fields. When there is a change in one of the records of table 'A', I would like to copy the record with it's new values to table A_back. Thanks Sent via Deja.com http://www.deja.com/
Sample code will be very appreciated... > Hi, > > Is there anyway for duplicate a record, using triggers, from one table > to another table in case of any changes in the first table? > > Let say I have a table 'A' with two fields and another table, 'A_back' > with the exact two fields. > When there is a change in one of the records of table 'A', I would like > to copy the record with it's new values to table A_back. > > Thanks > > Sent via Deja.com > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/
> Is there anyway for duplicate a record, using triggers, from one table
> to another table in case of any changes in the first table?
>
> Let say I have a table 'A' with two fields and another table, 'A_back'
> with the exact two fields.
> When there is a change in one of the records of table 'A', I would
> like to copy the record with it's new values to table A_back.
Yes. With just two columns is isn't too cumbersome even.
Unfortunately, I don't think there is any syntax to refer
to an entire row; you have to refer to each new column value
individually.
Now the exact syntax needed will depend on what exactly you meant
by "When there is a change in one of the records of table 'A', I would
like to copy the record with it's new values to table A_back."
I assume you mean you want to update A_back when A is modified,
not that you want to insert a new copy of the row into A_back
for every change to A.
Anyway, you'll need something like the following. I assume
that 'col1' is a unique key for the table.
create trigger trig_Ai
insert on A
referencing new as new
for each row
(insert into A_back values (new.col1, new.col2));
create trigger trig_Au
update on A
referencing new as new old as old
for each row
(update A_back set col1 = new.col1, col2 = new.col2
where col1 = old.col1);
create trigger trig_Ad
delete on A
referencing old as old
(delete from A_back where col1 = old.col1);
HTH,
-cs
Sent via Deja.com
http://www.deja.com/
> create trigger trig_Ad
> delete on A
> referencing old as old
> (delete from A_back where col1 = old.col1);
Sorry, I left out "for each row" when typing
the above. It should say:
create trigger trig_Ad
delete on A
referencing old as old
for each row
(delete from A_back where col1 = old.col1);
-cs
Sent via Deja.com
http://www.deja.com/