Display and save a non-database item in Oracle form

[Ucaihc]

I am developing an Oracle form and would like to display an item so that it is equal to a the value in a database field plus 100. Also, when the user enters a value in this field, I would like to subtract 100 before storing it in the database. For example: select colvalue +100 from x going into the form, and insert y -100 going into the table?

What code would I use and in which triggers

 

[Imacubfan]

Assumptions:

1. You intend to subtract 100 for INSERT AND UPDATE, else the timing statement (line 2) must be modified.

2. Your OLD/NEW references are default values, else you must include a REFERENCING clause before the BEGIN statement. I suspect default values will work.

3. There is some reason why you are not using client-side code or some other middle tier DLL for your business logic.


CREATE OR REPLACE TRIGGER MYTABLE_BIUR
BEFORE INSERT OR UPDATE
ON TABLE MYTABLE
FOR EACH ROW
BEGIN

:NEW.COLVALUE := NEW.COLVALUE - 100);

END;
/


There is no "BEFORE SELECT" trigger, so you could create a VIEW that would provide the desired values:


CREATE OR REPLACE VIEW MYTABLE_VIEW1 AS
SELECT
, COLUMN_1
, COLUMN_2
, COLVALUE + 100 AS COLUMN_3
FROM MYTABLE
/