Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy rows as they inserted on a replica table using trigger

Status
Not open for further replies.

evgeniosc

IS-IT--Management
Oct 5, 2002
75
CY
I would like to use a trigger to copy each inserted row in a table named Test1 to a table Test1_Copy.

Because of the following reasons I would like a simple trigger that would not reference each column:
1. the table contains many columns
2. I do not want to change the trigger each time I change the table structure (i will change both tables)
3. I do not want to select from the source table as this will acquire locks on table (it's an online application and I do not want to get unnecessary locks)

My first try was:

CREATE TRIGGER Test1_Insert INSERT ON Test1
REFERENCING new as newRow
FOR EACH ROW
(
INSERT INTO Test1_Copy SELECT * FROM Test1 WHERE Id=newRow.Id
);

But this get some locks and has unneeded overhead. Since I have all the data inserted with the newRow correlation why to go and find the record again from the table.

Can I use the correlation name in a way to not find the data again? e.g

CREATE TRIGGER Test1_Insert INSERT ON Test1
REFERENCING new as newRow
FOR EACH ROW
(
INSERT INTO Test1_Copy values(newRow.*)
);

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top