Re: Shortcut for defining triggers

On Mon, Jan 24, 2005 at 08:12:49AM -0800, David Fetter wrote:
> On Sun, Jan 23, 2005 at 03:49:22PM -0600, Jim C. Nasby wrote:
> > Sorry if this is old, but I couldn't find it in the archives...
> >
> > How difficult would it be to provide a means to define a trigger in
> > one statement? Something like a combination of CREATE TRIGGER and
> > CREATE FUNCTION? Being able to define them seperately is awesome for
> > generic cases where you can use one function for a bunch of
> > different tables, but it's a pain in the cases where you need a
> > unique trigger for one table.
>
> What would you want the function name to default to? What language,
> or would you want to specify that somehow?
>
> Here's a sketch of what such an API might look like:
>
> CREATE TRIGGER foo_trg
> BEFORE INSERT OR UPDATE ON foo_tab
> FOR EACH ROW EXECUTE PROCEDURE
> LANGUAGE PLPGSQL (/* params would go here if any */) $$
> /* body here */
> $$;
>
> This would cause a foo_tab_b4_iu_func (how to address namespace
> collisions?) to be created in the appropriate language with
> appropriate params, then the foo_trg on the table.

Yes, that's what I was thinking. If we wanted to get really clever,
theoretically the function wouldn't even need to be named, but of course
that would mean having to different sets of trigger code, which is
probably a BadIdea(tm). As for the function name, it seems you'd want
the trigger name in the function name somewhere.

> Does SQL:2003 have anything to say about this? Also, what kind of
> development effort would be involved with an implementation, assuming
> SQL:2003 doesn't forbid?

Does the SQL standard even address triggers that only call a function?
PostgreSQL is the only database I've used that does this (all the other
ones just have you provide the procedural code you want run when the
trigger fires).
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"