oracle - Populate a column on update (create too?), and why "FOR EACH ROW"? -

oracle - Populate a column on update (create too?), and why "FOR EACH ROW"? -

i have table of people belong various sites. these sites can change, don't often. when create attendance record (a learner_session object) don't store site. has cause problem in reporting how many training hours site has, because people have changed sites on years. not much, we'd right.

so i've added site_at_the_time column learner_session table. want auto-populate site person @ when attended session. i'm not sure how reference this. reason (i'm guessing speed development or something) learner_id allowed null. i'm planning update trigger. learner_id shouldn't ever updated, , if ever did somehow, entire record junk i'm not worried overwriting it.

the trigger have

create trigger set_site_at_the_time after update of learner_id on lrn_session begin :new.site_at_the_time:= (select site_id learner :new.learner_id = who.learner_id); end;

which leads me next error:

ora-04082: new or old references not allowed in table level triggers

now, i've done research , found need utilize for each row - , i'm wondering for each row - every row captured trigger? or every row in table?

also, trigger when create record too? if insert learner_session(id,learner_id,...) values(learner_session_id_seq.nextval,1234,...) capture appropriately?

and while i'm here, might see if there's else i'm doing wrong trigger. i'm asking figure out each row supposed , if triggers properly. =)

for each row means trigger fire 1 time each row updated sql statement. without clause, trigger fire once, no matter how many rows affected. if want alter values they're beingness inserted, have utilize for each row, because otherwise trigger can't know :new , :old values use.

as written, trigger fires on update. create fire upon insert, you'd need alter definition:

create trigger set_site_at_the_time before insert or update of learner_id on lrn_session each row begin select site_id :new.site_at_the_time learner :new.learner_id = who.learner_id); end set_site_at_the_time;

oracle