Re: Manual Trigger Creation

On Mon, 19 Mar 2001, Josh Berkus wrote:

> Tom, Stephan. List folks:
> 
>     I'm having a great deal of trouble with the automated constraint
> triggers as implemented in 7.1 beta 3. I find that if I establish a
> foriegn key through the CREATE TABLE statement, things start to blow up
> if I modify any of the tables involved, and there's no easy way to drop
> and re-create the foriegn key.  (PostgreSQL isn't alone in this ... MS
> SQL Server is a headache if you want to tinker with the relational
> structure).

Yeah, it doesn't play nice with alter table at all. :(
Actually, for recreating -- All you really need to do is kill the
three triggers that it creates (drop trigger should work) and use
alter table to add them again.

>      As such, I'd like to manually create my own foriegn key triggers
> instead, once I'm done tinkering with the DB structure.  Can anyone
> provide me with an example of this?  I figure I'll need 5 triggers for
> each key:
> 
> 1. ON UPDATE trigger on Reference List
> 2. ON DELETE trigger on Reference List
> 3. ON UPDATE trigger on data table
> 4. ON DELETE trigger on data table
> 5. ON INSERT trigger on data table

You could do this (4 is unnecessary and 3 and 5 can be combined), although
I think you might be better off using alter table add constraint to do
that.