How to change column's value after record is updated in Oracle APEX

0
votes

I have a table with special columns like Inserted(Date), Updated(Date), Version(Number).

Inserted - when the record was created (Sysdate).
Updated - the latest record's update (Sysdate).
Version - default 1. Must be increased by 1 every time record is updated.

How can I make this work in Oracle Apex if I insert/update data via forms? I do not use those columns in forms, but when I update other columns, I want dates / version to be updated automatically.

Somehow Inserted works with dynamic action "Set value on page load", but it didn't work with Updated.
I also tried with triggers, but it didn't help as well.

Which way would be best to make it work?

1
oracletriggersoracle-apex
Database triggers will be fine as long as these columns aren't included on the APEX page. – Scott

1 Answers

0
votes

You can create two trigger one for insert and an other for update

CREATE OR REPLACE TRIGGER <table_name>_before_insert
BEFORE INSERT
   ON <table_name>
   FOR EACH ROW
    BEGIN
   -- Update create date field to current system date
   :new.<Inserted > := sysdate;
   :new.<Updated> := sysdate; 
   :new.<Version> := 1;
END;


CREATE OR REPLACE TRIGGER <table_name>_before_update
BEFORE UPDATE
   ON <table_name>
   FOR EACH ROW

BEGIN
   -- Update update date field to current system date
   :new.<Updated> := sysdate;
   :new.<Version> := :old.<Version> + 1;
END;