In our database we want to keep track of every change made to it. So for table TBLMTUSFIBROID, we also have an TBLMTUSFIBROID_HISTO table to keep track of all these changes. To do that, I created a trigger:
CREATE OR REPLACE TRIGGER TBLMTUSFIBROID_HISTO
after update on TBLMTUSFIBROID
for each row
begin
insert into TBLMTUSFIBROID_HISTO(SITE, RSID, DATE_ENTERED, INITIALS, FIB1, FIB2A, FIB2B, FIB2C, FIB2D, FIB2E,FIB2F, FIB2G, FIB2H, FIB2I, FIB2J, FIB2K, FIB3A, FIB3B, FIB3C, FIB3D,FIB4A, FIB4B, FIB4C, FIB4D, FIB5, FIB6, FIB7A, FIB7ATEXT, FIB7B, FIB7C, FIB7D, FIB7DTEXT, FIB7E, FIB8, FIB9, FIB11, FIB12, FIB13, FIB14, FIB15, FIB16, FIB17,TYPE_US, FIB1_FOLLOWUP, DATE_UPDATE, FIBTEMPPP4)
values(:old.SITE, :old.RSID, :old.DATE_ENTERED, :old.INITIALS, :old.FIB1,:old.FIB2A, :old.FIB2B, :old.FIB2C, :old.FIB2D, :old.FIB2E,:old.FIB2F, :old.FIB2G, :old.FIB2H, :old.FIB2I, :old.FIB2J,:old.FIB2K, :old.FIB3A, :old.FIB3B, :old.FIB3C, :old.FIB3D,:old.FIB4A, :old.FIB4B, :old.FIB4C, :old.FIB4D, :old.FIB5,:old.FIB6, :old.FIB7A, :old.FIB7ATEXT, :old.FIB7B, :old.FIB7C, :old.FIB7D,:old.FIB7DTEXT, :old.FIB7E, :old.FIB8, :old.FIB9, :old.FIB11, :old.FIB12,:old.FIB13, :old.FIB14, :old.FIB15, :old.FIB16, :old.FIB17,:old.TYPE_US, :old.FIB1_FOLLOWUP, :old.DATE_UPDATE, :old.FIBTEMPPP4);
end TBLMTUSFIBROID_HISTO;
However, this is not an elegent way. I have to check the long list to make sure every variable is in there. If a new variable is added to TBLMTUSFIBROID table, I have to come back and add it in the trigger as well. I am just wondering whether there is an better way to achieve the same goal. I tried the following code:
CREATE OR REPLACE TRIGGER TBLMTUSFIBROID_HISTO
after update on TBLMTUSFIBROID
for each row
begin
insert into TBLMTUSFIBROID_HISTO
as select * from TBLMTUSFIBROID;
end TBLMTUSFIBROID_HISTO;
Apparently it doesn't work. Any thoughts or suggestions?
|
] 
Blog
Search
Help
Members
Register
Login
Home
