Dependancies on Tables

From: "Bryan Zera" <Bryanz(at)pollstar(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Dependancies on Tables
Date: 2003-06-26 19:44:10
Message-ID: sefaeaac.041@groupwise6.pollstar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have one main table for our users, as well as several related tables
that contain user information. We would like to set up triggers so that
the following occurs:

1. If someone deletes a user from the user table, it deletes all the
occurences of the user's information in all the related tables.
2. If someone tries to delete an entry from a related table, it won't
allow it.

While I had no problem creating triggers and functions that delete the
related table data when a user is deleted, I cannot find an elegant way
to prevent the autonomous deletion of data from the related tables.

Originally, I thought I would just add triggers/functions to the
related tables so that if someone tried to delete them, it would not
allow the user to delete. But then I realized that if I added a
trigger/function combination to the related tables that prevents
deletion, the functions that are triggered by the user deletion would
also be blocked from deleting from that table.

The only solution I could come up with is to revoke delete priveleges
on all users for the related tables, create a new user for the express
purpose of deletion from the related tables, grant this new user delete
priveleges on the related tables, and run the trigger functions under
that username.

Can anyone suggest a more elegant solution, save for writing the
functions in C?

Thanks,
Bryan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jay O'Connor 2003-06-26 19:49:11 deleting procs
Previous Message Steve Crawford 2003-06-26 19:33:51 Re: How many fields in a table are too many