i want to set column value in IG when user press Save. i have 2 columns 'created_by' and 'updated_by', now when my primary key column :P8_ID is null then should set 'created_by' column value using application item :SESSION_USER_ID which was set at login and 'updated_by' when :P8_ID is not null. i can set using process on a Form where 'Page Items' are available but how to do the same for IG columns? please guide. --using Apex 21.1--
1 Answer
A common practice in apex applications is to have the audit columns on a table (created (date), created_by, updated (date) and updated_by) set by a trigger. The advantage of this approach is that this is transparent in your application. You don't have to worry about the audit columns anywhere in the application where insert/updates are done.
Example on a dummy table:
create or replace trigger test_table_biu
before insert or update
on test_table
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end test_table_biu;
/
3 Comments
Ahmed Haroon
thanks @Koen Lostrie... we are using custom authentication and all users are in our own table. :SESSION_USER_ID column set after successful login which needs to update during DML operations (Delete is not allowed). for created_at and updated_at we wrote trigger before insert or update which works fine as you already mentioned. i think we have to write own pl/sql code for DML operations on the related page... please advise.
Koen Lostrie
use
V('SESSION_USER_ID') in the trigger instead of 'APP_USER' - the trigger should fire on any dml I don't understand why you'd have to write custom codeAhmed Haroon
thanks @Koen Lostrie for guidance and help. will try to follow the instructions. regards