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(at)kidd(dot)org(dot)uk
----------------------------------------------------------------