MacLochlainns Weblog

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 COMMIT or 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;
/

4. A 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.