I am powering a form with a view that joins a table on another table containing file information (i.e. BLOB, filename, mimetype, and last updated). Kind of like this:
CREATE VIEW myview AS
SELECT
mytable.*,
files.*
FROM mytable,files
WHERE mytable.file_id = files.file_id;
I then have two triggers attached to the view: an instead of update and an instead of insert. I do this so that I can populate both tables at the same time. Looks something like this:
CREATE OR REPLACE TRIGGER myview_insert
instead OF INSERT ON myview
referencing new AS new old AS old
DECLARE v_file_id number;
BEGIN
SELECT file_seq.NEXTVAL INTO v_file_id FROM dual;
INSERT INTO files (file_id, file_data, filename, mimetype, last_updated)
VALUES (v_file_id, :new.file_data, :new.filename, :new.mimetype, :new.last_updated);
INSERT INTO mytable (my_value, file_id)
VALUES (:new.my_value, v_file_id);
END;
The instead of update trigger works great! The insert trigger kind of works... Values not tied to the BLOB (files table) are inserted correctly, however, all BLOB values (raw data, filename, mimetype, and last updated) are all empty/null.
The BLOB format mask on the file page item is set up correctly because the update trigger is working perfectly.
My question is: am I trying to do something that ApEx just doesn't support? and if not, how can I get it to work?!