Access "Page Items to Submit" values inside a trigger in oracle apex

Question

I want to access extra page item value from a trigger which isn't in the related data table. For an example I have a table like below,

      Employee
----------------------

empId | fName | lName 
----------------------
1     | John  | Doe
----------------------
2     | Jane  | Doe

Apex form items will be like,

P500_EMPID, P500_FNAME, P500_LNAME

I have an extra page item called P500_SOMEIDSwhich is a multi select list. I want to access those selected values inside After Insert trigger of the Employee table. I tried to add this item into "Page Items to Submit". But I do not know how to access it inside that trigger. Is it possible..? and How..?

how are you inserting the data in the employee table. Are you using an apex form region ? — Koen Lostrie, Jun 23 '20 at 06:44

score 2 · Accepted Answer · answered Jun 23 '20 at 07:21

In the page process that handles your table update (that will be a process of type "Form - Automatic Row Processing (DML)", under "Settings" there is a attribute "Return Primary Key(s) after Insert". If that is set to "On", then the insert statement will return the value of the inserted row into the page item that is defined as your primary key. Example:

Create a form on emp
Make P1_EMPNO the primary key page item
Suppose you create a new row, and that empno value is 1000. Then after the automatic row processing page process is run, the value of P1_EMPNO is set to 1000.

If you want to insert rows into another table referencing the newly created empno then you can create a page process (that executes after the row processing) of type pl/sql code like this:

BEGIN
  INSERT INTO some_other_table (empno) VALUES (:P1_EMPNO); 
END;

Using triggers for business functionality should be avoided whenever possible.

Thanks for the answer Koen. The page item I mentioned in the question is a multi select page item. So it returns data set like "1","3","5". I want to put these numbers inside an IN () and separated by comma and as numbers not as strings . Can you help me with that..? — Pumayk26, Jun 23 '20 at 10:17
That is a different question altogether. Can you post a new one pls ? — Koen Lostrie, Jun 23 '20 at 10:55

score 1 · Answer 2 · answered Jun 23 '20 at 05:52

1

Instead of a database trigger, use a stored procedure which will do the same job. Pass any number of parameters you want (including the P500_SOMEIDS item).

answered Jun 23 '20 at 05:52

Littlefoot

131,892
15
35
57

Thanks for the answer. Can you tell me how to access that item..? – Pumayk26 Jun 23 '20 at 05:54
I used a trigger because I want to insert data into another table with inserted id. After insert trigger. – Pumayk26 Jun 23 '20 at 05:55
Don't use a trigger. Use a stored procedure and invoke it from a page process in your apex page after submit. – Koen Lostrie Jun 23 '20 at 06:21

Access "Page Items to Submit" values inside a trigger in oracle apex

2 Answers2