Thread: Manual Trigger Creation
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). 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 Am I on the right track? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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.
Stephan, > 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. How can I drop them if they are <unnamed> triggers? I've been doing that by editing pg_trigger, but that just got me into a system table mess that it took 2 hours to fix ... and lost me half my foriegn keys to boot. Is there, perhaps, a way I can name my constraints in the original CREATE TABLE statement? Aha! I see ... I never noticed the optional [CONSTRAINT constraint_name] phrase before. 'S what I get for crossing over from Transact-SQL without retraining! > 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. I'm interested in the approach for another reason. I have a number of tables that must match a NON-UNIQUE value in a reference table, and thus I'd like to test them against a query or view. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> 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?
pg_dump some tables with foreign keys ...
regards, tom lane
On Mon, 19 Mar 2001, Josh Berkus wrote: > Stephan, > > > 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. > > How can I drop them if they are <unnamed> triggers? I've been doing > that by editing pg_trigger, but that just got me into a system table > mess that it took 2 hours to fix ... and lost me half my foriegn keys to > boot. You can use the real trigger name (tgname) and drop trigger, but you need to double quote the name: drop trigger "RI_ConstraintTrigger_<n>" on <table>; ... (for all three) > Is there, perhaps, a way I can name my constraints in the original > CREATE TABLE statement? Aha! I see ... I never noticed the optional > [CONSTRAINT constraint_name] phrase before. 'S what I get for crossing > over from Transact-SQL without retraining! > > 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. > > I'm interested in the approach for another reason. I have a number of > tables that must match a NON-UNIQUE value in a reference table, and thus > I'd like to test them against a query or view. Well, if you're going against non-unique values then the referential actions aren't really meaningful anymore [you'd have to look at match partial's semantics, probably], and you'd probably need to reimplement that part. In general you can add the constraint triggers the same way pg_dump does.