Trigger - instead of update insert
Hi,
I have a requirement to turn all updates into a table into inserts.
So essentially, for each row update; -
Undo what has been done; -
:NEW.fieldname:= :OLD.fieldname;
And instead insert a record which represents the difference; -
insert into mytable
(id,
value)
(mytable_s.nextval,
:NEW.value - :OLD.value);
(Above is extremely simplified, but that is the gist of it)
When I create this trigger it compiles without error - but when I then attempt an update the update errors telling me that the trigger has errors....
Am I breaking some fundamental rule (I know triggers are not recommended - but aside from that) - or should I be using a difference approach - like creating a view and pointing my updates at the view, and then having a similar trigger on the view???
I have a requirement to turn all updates into a table into inserts.
So essentially, for each row update; -
Undo what has been done; -
:NEW.fieldname:= :OLD.fieldname;
And instead insert a record which represents the difference; -
insert into mytable
(id,
value)
(mytable_s.nextval,
:NEW.value - :OLD.value);
(Above is extremely simplified, but that is the gist of it)
When I create this trigger it compiles without error - but when I then attempt an update the update errors telling me that the trigger has errors....
Am I breaking some fundamental rule (I know triggers are not recommended - but aside from that) - or should I be using a difference approach - like creating a view and pointing my updates at the view, and then having a similar trigger on the view???
0