oracle - SQL Trigger on field update to insert new record in another table -

i trying keep record of changes data in table. i've read there various ways of doing sound lot more convenient, in situation want implement functionality using trigger. have 2 tables:

create table applications (   application_id int not null,   student_id int not null,   job_id int not null,   applicationchange_type varchar(64) not null,    primary key (application_id),   foreign key (student_id) references students(student_id),   foreign key (job_id) references jobs(job_id),    constraint ck_type check (applicationchange_type in ('submitted', 'withdrawn', 'invited interview', 'invited assessment centre', 'rejected', 'accepted')) ); 

and:

create table applicationchanges (   applicationchange_id int not null,   application_id int not null,   applicationchange_type varchar(64),   applicationchange_datetime date not null,    primary key (applicationchange_id),   foreign key (application_id) references applications(application_id),    constraint ck_apptype check (applicationchange_type in ('submitted', 'withdrawn', 'invited interview', 'invited assessment centre', 'rejected', 'accepted')) ); 

i create trigger when applicationchange_type field of record in applications table edited/updated, new record created in applicationchanges table. need carry on application_id , applicationchange_type edited record in applications table in new record in applicationchanges. applicationchange_datetime field should populated current date.

at current, primary keys of both tables use sequence , trigger auto id number on creation of new record.

any input appreciated! , apologies if i've been @ unclear.

edit:

also have made attempt @ this, i'm unsure of , seems broken didn't think there point in including it. here is:

create or replace trigger applicationsupdatetrigger after update on applications begin   insert applicationchanges (application_id, applicationstatus_type, applicationchange_type)   select application_id, applicationstatus_type applications     join inserted on inserted.application_id = applications.application_id   insert applicationchanges (application_id, applicationstatus_type, applicationchange_datetime)   values (null, application_id, applicationstatus_type, getdate()) end; / 

i'll stick oracle question tag says (though using getdate() in sample trigger suggests sql server).

i'm not sure applicationchange_type value want store in applicationchanges table: 1 before change (old one) or after change (new one)?

let's cover both variants, assuming sequence , trigger auto incrementing id columns works fine.

1. the old value:

   create or replace trigger applicationsupdatetrigger after update on applications each row begin   if (:old.applicationchange_type null , :new.applicationchange_type not null) -- 1 redundunt long you've got not null constraint on application.applicationchange_type column      or (:old.applicationchange_type <> :new.applicationchange_type)       insert applicationchanges (application_id, applicationchange_type, applicationchange_datetime)     values (:old.application_id, :old.applicationchange_type, sysdate);   end if; end; / 

2. the new value:

   create or replace trigger applicationsupdatetrigger after update or insert  on applications each row begin   if (:old.applicationchange_type null , :new.applicationchange_type not null) --this check not redundant (:old.applicationchange_type null on inserts)      or (:old.applicationchange_type <> :new.applicationchange_type)        insert applicationchanges (application_id, applicationchange_type, applicationchange_datetime)     values (:new.application_id, :new.applicationchange_type, sysdate);   end if; end; / 

differences quite cosmetic: second variant works on both update , insert , uses :new record values instead of :old ones.