Обсуждение: Dependancies on Tables
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
On Thu, Jun 26, 2003 at 12:44:10 -0700, Bryan Zera <Bryanz@pollstar.com> wrote: > > 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. Can't the trigger check to see if the user record is still there and only block the deletion if it is? You will also want an update trigger to make sure the user field can't be changed.
> > 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. > > > Can't that be handled with foreign key constraints, ala > > CASCADE > > Automatically drop objects that depend on the dropped column or constraint > > RESTRICT > Refuse to drop the column or constraint if there are any dependent > objects. This is the default behavior. It would be nice if I could do it with foreign keys. If I use a foreign key on the related tables and specify 'ON DELETE CASCADE", the items from the related tables are removed on the deletion of users (which is correct), but I am still able to delete items from the table individually. The way I see it, I'm going to need to create columns for references to the related tables within the user table and then use a foreign key on those columns in the user table as well. That way, if you attempt to delete an item from a related table, it sees that there is still an entry in the users table and doesn't allow for deletion. Is there a way to use foreign keys in the way that I described above without having to create reference columns on the user table?