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 INTEGER REFERENCES 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
