pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Manual Trigger Creation
5+ messages / 3 participants
[nested] [flat]

* Manual Trigger Creation
@ 2001-03-19 22:48  Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Josh Berkus @ 2001-03-19 22:48 UTC (permalink / raw)
  To: pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



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

* Re: Manual Trigger Creation
@ 2001-03-19 22:58  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Stephan Szabo @ 2001-03-19 22:58 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

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.




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

* Re: Manual Trigger Creation
@ 2001-03-19 23:34  Josh Berkus <josh@agliodbs.com>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2001-03-19 23:34 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



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

* Re: Manual Trigger Creation
@ 2001-03-19 23:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2001-03-19 23:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"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



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

* Re: Manual Trigger Creation
@ 2001-03-19 23:49  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2001-03-19 23:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql


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.




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


end of thread, other threads:[~2001-03-19 23:49 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-19 22:48 Manual Trigger Creation Josh Berkus <josh@agliodbs.com>
2001-03-19 22:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-19 23:34   ` Josh Berkus <josh@agliodbs.com>
2001-03-19 23:49     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-19 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>

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