We had an APEX project where we wanted to add auto-generated key, and save both date/time and user that made the insert and last update.In Oracle, the basic idea is to use a trigger and (for the key) a sequence.
I was sure I will find lots of examples, but didn't find one that seemed like perfect fit for me. So I got some help from my colleague Matan Poran, who has a rare combination of both great professionality and personality.
Lets start with this link: https://www.insum.ca/feuertip-41-tips-for-triggers/
I will have here code for the following:
- Adding the 4 "create/update time/user" columns to table.
- Adding trigger that deals only with those 4 columns with Apex users.
- Creating a sequence.
- Adding trigger that deals with those 4 columns with Apex users and inserting the sequence based auto-generated key.
Modify "Your_Table" by adding 4 "create/update time/user" columns
alter table "YOUR_TABLE" add (
"CREATE_DATE" DATE,
"CREATED_BY" VARCHAR2(128),
"UPDATE_DATE" DATE,
"UPDATED_BY" VARCHAR2(128) );
Adding trigger that deals only with those 4 columns with Apex users
create or replace TRIGGER "Your_Trigger_Name"
before
insert or update on "YOUR_TABLE" --
for each row
begin
if inserting then
:new.CREATE_DATE:=SYSDATE;
:new.CREATED_BY:=nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
if UPDATING then
:new.UPDATE_DATE := SYSDATE;
:new.UPDATED_BY := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
end;
/
If you are not in an APEX case you can use your user definition. For example in Oracle SQL:
create or replace TRIGGER "Your_Trigger_Name"
before
insert or update on "YOUR_TABLE" --
for each row
begin
if inserting then
:new.CREATE_DATE:=SYSDATE;
:new.CREATED_BY:=trim(upper(user));
end if;
if UPDATING then
:new.UPDATE_DATE := SYSDATE;
:new.UPDATED_BY := trim(upper(user));
end if;
end;
/
We can complicate it a bit. For example setting value for CREATED_BY only if the user wasn't updated manually:
create or replace TRIGGER "Your_Trigger_Name"
before
insert or update on "YOUR_TABLE" --
for each row
begin
if inserting then
:new.CREATE_DATE:=SYSDATE;
if :new.CREATED_BY is null then :new.CREATED_BY:=trim(upper(user)); end if;
end if;
if UPDATING then
:new.UPDATE_DATE := SYSDATE;
:new.UPDATED_BY := trim(upper(user));
end if;
end;
/
* Yes, this code works for multiple updates.
** Please note that trigger names are case sensitive!
The following enhances the above code to include Auto-generated primary key based on sequence.
Creating a sequence
If you are in APEX you can create a sequence by using right click in sequences in Object Browser.
anyway the basic sequence code for creating the sequence named YOUR_TABLE_SEQ is:
create sequence "YOUR_TABLE_SEQ"
start with 1
increment by 1
cache 20
nocycle;
Adding trigger that deals with those 4 columns with Apex users and inserting the sequence based auto-generated key
Assuming there is a column KEY_COL used as primary key and you want the sequence to update it. I will use the previous sequence.
All is needed is one extra row:
create or replace TRIGGER "Your_Trigger_Name"
before
insert or update on "YOUR_TABLE" --
for each row
begin
if inserting then
if :new.KEY_COL is null then :new.KEY_COL:=YOUR_TABLE_SEQ.nextval; end if;
:new.CREATE_DATE:=SYSDATE;
:new.CREATED_BY:=nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
if UPDATING then
:new.UPDATE_DATE := SYSDATE;
:new.UPDATED_BY := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end if;
end;
/
P.S.
Please note that sometime the sequence based key can be a security issue when used in URL (then, if security not hardened enough, user can try the next value in the URL). In APEX this is solved by using "Session State Protection", so generated link will also include a checksum. See more about it in the following Blog: https://hartenfeller.dev/blog/apex-security-sequences-risk.
(In few words: Enable Session State Protection in shared components under security, on page level setting - security: for Page Access protection, set "Arguments Must Have Checksum".)
