Need help creating a BEFORE DELETE trigger

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Need help creating a BEFORE DELETE trigger
2+ messages / 2 participants
[nested] [flat]

* Need help creating a BEFORE DELETE trigger
@ 2003-06-30 21:45  Michael A Nachbaur <mike@nachbaur.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael A Nachbaur @ 2003-06-30 21:45 UTC (permalink / raw)
  To: pgsql-sql

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. "

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Need help creating a BEFORE DELETE trigger
@ 2003-07-01 00:41  Rod Taylor <rbt@rbt.ca>
  parent: Michael A Nachbaur <mike@nachbaur.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2003-07-01 00:41 UTC (permalink / raw)
  To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql

> 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

It will only be able to delete the row (and cascade) once per row.  The
second time it tries to find the row, the row won't exist anymore.
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-07-01 00:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-30 21:45 Need help creating a BEFORE DELETE trigger Michael A Nachbaur <mike@nachbaur.com>
2003-07-01 00:41 ` Rod Taylor <rbt@rbt.ca>

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