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:
While I could build an
ON DELETE trigger, I found that while the
COMMIT deletes the rows but 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:
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:
3. Then, I built a trigger to commit work, delete external files, and cleanup the FILE_LIST table. It is this:
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:
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.
2. Query the
FILE_LIST to see that a copy of the
BFILE locator exists:
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.