Re: How to fire triggers just on "top" level DML

On Jan 19, 2011, at 4:59 PM, Kevin Grittner wrote:

> We've been running for about ten years on a framework which fires
> triggers similar to database triggers in a Java tier close to the
> database, and we're now trying to convert these to actual PostgreSQL
> database triggers. Our biggest hitch at the moment is that we
> defined a class of triggers we called "top" triggers, which only
> fire from DML submitted by the application, not from DML issued by
> other triggers.
>
> One significant use of this is to block direct modification of
> summary data (either selected columns or entire tables) which are
> supposed to be trigger maintained. It's not immediately obvious how
> to accomplish this within PostgreSQL, although I'm probably missing
> something. We're not tied to any particular methodology -- a
> TG_DEPTH variable, if it existed, would do fine, for example.
>
> Any suggestions?

Most PLs include some session-specific storage. In PL/Perl, it is %_SHARED. Setting a flag there should do the trick. If you are using a PL which does not have such a notion (like plpgsql), you can add a call in your triggers to a function written in a PL which does support this. Alternatively, a C function which sets/checks a global flag would work as well.

Cheers,
M