Both table are identical in structures. I have an application that will do insert only to table TableLive, right now I don't have any conditions in the application.
I want to a have a trigger, where :
1. during INSERT into TableLive, if the REGISTRATIONDATE > :NEW.REGISTRATION don't insert into TableLive, but insert into TableAudit
2. during INSERT into TableLive, if the REGISTRATIONDATE < :NEW.REGISTRATION insert into TableLive but delete and insert old record into TableAudit
The objective is to have only unique STAFFID and the latest what he did, the latest is based on registration date, this registration is free datetime
Your design is flawed. Your should have a parent table that contains all the staff information, but no regestration information and a child table that contains all the registration information for the staff. Then a simple quest where you find the highest registrationdate will find the last row.
You do not need a parachute to skydive. You only need a parachute to skydive twice.