From: | Neil Kidd <neil(at)kidd(dot)org(dot)uk> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Rules or triggers. |
Date: | 2001-03-19 18:43:25 |
Message-ID: | 4.3.2.7.2.20010319183219.00ae9ce0@ms.webfactory.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
----------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Williams | 2001-03-19 21:27:11 | PosgreSQL Windows install |
Previous Message | Mike Friesen | 2001-03-19 15:57:21 | General Cursors Question |