From: Michael A Nachbaur <mike@nachbaur.com>
To: pgsql-sql@postgresql.org
Subject: Need help creating a BEFORE DELETE trigger
Date: Mon, 30 Jun 2003 14:45:06 -0700
Message-ID: <200306301445.06935.mike@nachbaur.com> (raw)
Hello everyone,
I'm creating two database tables that will be used to cache the results of a
search. Basically, when a search is initiated, an entry will be created in
the "Search" table that represents the search, and a single entry will be
created in a child table "SearchResults" for every result returned. A
foreign key relationship will be associated between the two.
CREATE TABLE Search
( id SERIAL
, accountid INTEGER REFERENCES account(id) ON DELETE CASCADE NOT NULL
, sessionnum CHAR(32) UNIQUE NOT NULL
, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
, sqlcode TEXT
);
CREATE TABLE SearchResults_Customer
( id SERIAL
, searchid INTEGER REFERENCES search(id) ON DELETE CASCADE NOT NULL
, customerid INTEGER REFERENCES customer(id) ON DELETE CASCADE
-- All the results go in fields here
);
Now, when any record is deleted in the SearchResults table (via an ON DELETE
CASCADE, or other trigger), I'd like the entire search set to be deleted
since the search is now invalid. Therefore, if a single record in the
SearchResults table is deleted, I want it to instead delete the associated
record in the Search table; this'll cause a CASCADE into the SearchResults
table, toasting my entire result set.
The problem I'm looking at is: could this cause a recursion problem, where the
cascading deletion will try to cause the whole thing to cascade again? How
can I set this up so I can kill an entire tree of data if any one of it's
members dies?
Thanks in advance.
--
/* Michael A. Nachbaur <mike@nachbaur.com>
* http://nachbaur.com/pgpkey.asc
*/
"I don't know, " said the voice on the PA, "apathetic
bloody planet, I've no sympathy at all. "
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@nachbaur.com
Subject: Re: Need help creating a BEFORE DELETE trigger
In-Reply-To: <200306301445.06935.mike@nachbaur.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox