Skip to Main Content

APEX

Declaritive support for BLOB in instead of insert trigger on a view

710907Jul 8 2009 — edited Sep 23 2009
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?!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2009
Added on Jul 8 2009
1 comment
309 views