Skip site navigation (1) Skip section navigation (2)

Re: FUNCTION, TRIGGER and best practices

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-sql(at)postgresql(dot)org, KeithW(at)narrowpathinc(dot)com
Cc: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: FUNCTION, TRIGGER and best practices
Date: 2004-12-17 05:43:32
Message-ID: 200412162143.32610.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
Keith,

> Can triggers and functions have the same name?

Yes.

> Is this a good practice?

It can be.   If the function does nothing but power the trigger, sure.   Or 
you can use prefixes or suffixes to distinguish them.  For example, if you 
had an audit trigger on the companies table, you might call the function 
audit_companies_tf, the trigger audit_companies_tg.

> Where should the function and trigger be stored?

Um, in the database?

> In the same schema as the table they are connected to?

I'm not sure you CAN put the trigger in a different schema from the table.  
It's a dependant object.

> I will now be attempting to expand this function to move data from two
> source tables to four or more target tables.  (Different source columns go
> to different tables.)  Is it possible to do a transaction/commit/rollback
> inside a function that is driven by a trigger so that if any portion of the
> transfer fails it is all rolled back?

Well, if any portion of a trigger fails, everything is rolled back; so is the 
insert, for a before trigger.  So that might be the way to handle the 
double-trigger situation .... except they'll be in two different tables, yes?  
So that'll require an explicit transaction wrapping the two inserts.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-novice by date

Next:From: Michael FuhrDate: 2004-12-17 06:52:09
Subject: Re: FUNCTION, TRIGGER and best practices
Previous:From: Keith WorthingtonDate: 2004-12-17 04:05:20
Subject: FUNCTION, TRIGGER and best practices

pgsql-sql by date

Next:From: Richard Sydney-SmithDate: 2004-12-17 06:23:32
Subject: Table History
Previous:From: Keith WorthingtonDate: 2004-12-17 04:05:20
Subject: FUNCTION, TRIGGER and best practices

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group