(FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Novice Postgresql-list <pgsql-novice(at)postgresql(dot)org>
Subject: (FOR EACH STATEMENT AFTER UPDATE) Triggers & Transactions
Date: 2006-08-29 23:14:49
Message-ID: 20060829231449.71556.qmail@web31807.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bruce Momjian 2006-08-29 23:42:16 Re: Difference between char and varchar
Previous Message Bruno Wolff III 2006-08-29 21:07:19 Re: Difference between char and varchar