Re: Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

On 5/22/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Mon, May 22, 2006 at 10:00:22AM -0500, Jim C. Nasby wrote:
> > > T-SQL has statement-level triggers, and they get used a lot (some big apps
> > > ONLY put code in triggers). Statement-level triggers are very efficient for
> > > maintaining aggregates; the closest PG has are rewrite rules.
> >
> > Yeah, I wish PostgreSQL had them. I've got clients that could certainly
> > make use of them.
>
> What are you referring to that is not supported currently?
>
> CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
> ON table FOR EACH STATEMENT
> EXECUTE PROCEDURE funcname ( arguments )

Each programming language that supports triggers has its own method for making
the trigger input data available to the trigger function. This input
data includes the
type of trigger event (e.g., INSERT or UPDATE) as well as any
arguments that were
listed in CREATE TRIGGER. For a row-level trigger, the input data also
includes the
NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and
DELETE triggers. Statement-level triggers do not currently have any way to
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
examine the individual row(s) modified by the statement.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

So, if user types:
DELETE FROM foo WHERE doh ='bar' and baf > 5;
(resulting, say with 5000 deleted rows)

...you can either create on delete trigger row level, which will:
UPDATE foo_stat SET count = count -1 WHERE doh='bar';
...which will be fired 5000 times.

The idea is that you could write a statement level trigger
which will count deleted rows and issue
UPDATE foo_stat SET count=count-5000 WHERE doh='bar';

Regards,
Dawid