As you may have noticed in the early page on How to delete an external file from SQL, the
ON DELETE trigger deletes the file on the first phase of a two-phase commit. By the way, you’ll need the code and instructions from that other post for this one. This means that while you can rollback the
BFILE locator value, you can’t recreate the file. It isn’t in the trash bin unfortunately.
I thought a
GLOBAL TEMPORARY TABLE would fill the role quite well because a
ROLLBACK deletes the contents of the table. I tested my theory by building the following table:
CREATE GLOBAL TEMPORARY TABLE file_list ( file_name BFILE ) ON COMMIT DELETE ROWS;
While I could build an
ON DELETE trigger, I found that while the
COMMIT deletes the rows it fails to trigger the event. It would have been so great if a regular
COMMIT could have made this work, but it didn’t.
I rebuilt the table as a normal table and tested my model successfully. The components to make this work are noted below:
1. The placeholder table lets you stuff a
BFILE locator their until you’re ready to
COMMIT the change:
CREATE TABLE file_list ( file_name BFILE );
2. Then, I built a trigger on the
ITEM table (taken from the Oracle Database 11g PL/SQL Programming book code). It inserts the BFILE locator in the FILE_LIST table:
CREATE OR REPLACE TRIGGER item_t1 BEFORE UPDATE OR DELETE ON item FOR EACH ROW BEGIN -- Transfer a file reference for deletion after a commit. IF :old.item_photo IS NOT NULL THEN INSERT INTO file_list VALUES (:old.item_photo); END IF; END;
3. Then, I built a trigger to commit work, delete external files, and cleanup the FILE_LIST table. It is this:
CREATE OR REPLACE TRIGGER file_list_t1 BEFORE UPDATE OR DELETE ON file_list FOR EACH ROW BEGIN -- Delete files after row deletion. delete_file(get_cannonical_local_bfilename(:old.file_name); END; /
ROLLBACK at this point undoes the transaction. The value in the
ITEM_PHOTO column is recovered, and the copy of the
BFILE locator is erased from the
FILE_LIST table. The problem is how to effect the desired behavior with the commit. I wrote the
FILE_ACTION_COMMIT procedure to manage that process, as shown:
CREATE OR REPLACE PROCEDURE file_action_commit IS BEGIN -- Commit base rows, physical files, and locator copies. COMMIT; DELETE FROM file_list; COMMIT; END file_action_commit;
A commit, delete and commit is probably overkill, but I wanted to convey the idea of the sequence if a
TEMPORARY GLOBAL TABLE would’ve worked. A delete and commit would work more efficiently.
Now that the environment is there, you should put a file named Raiders3.png in a virtual directory named IMAGES to perform this test. The testing steps are:
1. Run the following two
UPDATE statements. Alternatively, copy the query from the next step in between to see your handiwork. The second
UPDATE statement triggers the process.
UPDATE item SET item_photo = BFILENAME('IMAGES','Raiders3.png') WHERE item_id = 1055;
2. Query the
FILE_LIST to see that a copy of the
BFILE locator exists:
SELECT get_canonical_local_bfilename(item_photo) FROM item WHERE item_id = 1055;
3. Rollback the transaction and see everything is unchanged both in and out of the database, or execute the
FILE_ACTION_COMMIT procedure. It will synchronize the database with the external file system.
SQL> EXECUTE file_action_commit;
It would have been so much cleaner with a
GLOBAL TEMPORARY TABLE if they worked the way that I thought they did. At least, this works effectively and simply.