Hello,
I have a trigger that will delete records referring to an "events" table
upon deletion. I have used it without problems for a number of times:
CREATE OR REPLACE FUNCTION delete_event_cascade() RETURNS TRIGGER AS $$
BEGIN DELETE FROM event_citations WHERE event_fk = OLD.event_id; DELETE FROM participants WHERE event_fk =
OLD.event_id; RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER delete_event_cascade
BEFORE DELETE ON events FOR EACH ROW EXECUTE PROCEDURE delete_event_cascade();
The event_citations table is a cross-reference between events and
sources, and goes like this:
CREATE TABLE event_citations ( event_fk INTEGER REFERENCES events (event_id), source_fk
INTEGERREFERENCES sources (source_id), PRIMARY KEY (event_fk, source_fk)
);
I'm doing a little cleanup, and perform this query:
pgslekt=> select * from event_citations where source_fk=553;event_fk | source_fk
----------+----------- 2600 | 553 2592 | 553 2817 | 553 19919 | 553 19920 |
553
(5 rader)
Then I do an adjustment in the "sources" table:
pgslekt=> update sources set
parent_id=553,small_text='',large_text='23.04.1745 ved Solum: \"Abraham
Nielsøn Aafos 49 aar 5 dage\"' where source_id=554;
UPDATE 1
And then, as I try to delete event #2600, I get the following message:
pgslekt=> delete from events where event_id=2600;
ERROR: relation with OID 1141502 does not exist
CONTEXT: SQL statement "DELETE FROM event_citations WHERE event_fk= $1 "
PL/pgSQL function "delete_event_cascade" line 2 at SQL statement
I run a quick check:
pgslekt=> select * from event_citations where event_fk=2600;event_fk | source_fk
----------+-----------
(0 rader)
The record seems to have disappeared into thin air. There has not been
performed any inserts or updates in the database between the 'update'
and the 'delete' above. And the event won't go away.
This is hardly a practical problem, because an event that isn't linked
to a "person" through the "participants" table will never print
anywhere, and the referring "participant" disappeared. But I don't like
it anyway.
I'm running PostgreSQL 8.0.4.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE