Rules or triggers.

Hi all,
My problem is this:
I have three tables (amongst others) in a database with PostgreSQL 7.0.2 on
Mandrake 7.1.

CREATE TABLE tbl_modules
(
   module_code VARCHAR(10)  PRIMARY KEY,
   full_name      VARCHAR(255) NOT NULL,
   acronym       VARCHAR(10)   NOT NULL
);

CREATE TABLE tbl_files
(
   file_id_number SERIAL             PRIMARY KEY,
   file_name        VARCHAR(255) NOT NULL,
   upload_date    DATE                NOT NULL DEFAULT NOW(),
   file_data          OID                   NOT NULL,
   size_in_bytes  INTEGER,
   text_desc       TEXT,
   content_type   VARCHAR(100)
);

/* Interposing table between tbl_files and tbl_modules */
/* There is also a UNIQUE, 2 column index on this table*/
CREATE TABLE tbl_file_modules
(
   file_id_number  INTEGER  REFERENCES tbl_files
                               ON UPDATE CASCADE
                               ON DELETE CASCADE,

   module_code     VARCHAR(10) REFERENCES tbl_modules
                               ON UPDATE CASCADE
                               ON DELETE CASCADE
);

Now when I delete a row from 'tbl_modules' any related record in
'tbl_file_modules' is deleted as it should be.
The problem is there may be occasions where 'orphan' records are left in
'tbl_files'.

I have attempted to write a RULE as follows:
CREATE RULE delete_orphaned_files AS
ON DELETE TO tbl_file_modules
DO
   DELETE
   FROM tbl_files
   WHERE tbl_files.file_id_number
   NOT IN( SELECT DISTINCT file_id_number
           FROM tbl_file_modules
         );

Which does not work, it seems that the rule is applied before the original
delete has taken place.
Will this RULE be applied on a row or statement basis?
Is there a way to apply the rule after the original delete(s)?

I read in "PostgreSQL Introduction and Concepts" by Bruce Momjian that a
trigger may be a better alternative, so I tried this:
CREATE FUNCTION del_orphan_files()
RETURNS opaque
AS
'DELETE
FROM tbl_files
WHERE tbl_files.file_id_number
NOT IN( SELECT DISTINCT file_id_number
                FROM tbl_file_modules
              );

SELECT 1 AS ignore_this
'
LANGUAGE 'plsql';


CREATE TRIGGER trigger_del_orphan_files AFTER DELETE
     ON tbl_file_modules
     FOR EACH ROW
     EXECUTE PROCEDURE del_orphan_files();

and of course this doesn't work.
As the trigger is 'FOR EACH ROW' I figure this could be an expensive call.

I'm now stuck and would appreciate any help / ideas or advice.

I've RTFM and am none the wiser :-(

Thanks in advance,
Neil.
----------------------------------------------------------------
From:            Neil Kidd
Website:        http://www.kidd.org.uk
E-mail:           neil@kidd.org.uk
----------------------------------------------------------------