Forum Stats

  • 3,838,234 Users
  • 2,262,343 Discussions
  • 7,900,548 Comments

Discussions

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

710907
710907 Member Posts: 1
edited Sep 23, 2009 5:48AM in APEX Discussions
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?!

Answers

  • 546142
    546142 Member Posts: 9
    The filebrowse component will do an initial insert on your table, but without the items in the BLOB format mask.
    So the insert will only create a new empty record.
    After that an update of that empty record is done with the BLOB fields (blob, mime, updatedate, filename).

    So I guess you'll need to add some triggers to catch the data on the update.
This discussion has been closed.