Thread: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions

My question is:

In PostgreSQL, is it possible for a trigger function called by a STATEMENT LEVEL AFTER {any}
TRIGGER to roll back or commit an implied transaction of a single SQL Statement?

Here is the Context:

While reading "SQL For Smarties", I came across a pseudo code trigger function:

CREATE TRIGGER CheckManagers
AFTER UPDATE ON JobAsignments -- same for INSERT
IF 1 <= ALL (SELECT COUNT(*)
             FROM JobAssignments
             WHERE job_type = 99
             GROUP BY store_nbr)
THEN ROLLBACK;
ELSE COMMIT;
END IF;

The intent is to rollback any changes made to a table that violate certain design constraints.
This trigger was intended as a work around for RDBMS that have not yet implemented ASSERTIONS.

Regards,

Richard Broersma Jr.

On Tue, Aug 29, 2006 at 04:14:49PM -0700, Richard Broersma Jr wrote:
> In PostgreSQL, is it possible for a trigger function called by a
> STATEMENT LEVEL AFTER {any} TRIGGER to roll back or commit an
> implied transaction of a single SQL Statement?

An AFTER trigger can raise an exception to abort the operation.

--
Michael Fuhr