Need "InBetween" (not just Before and After) Trigger

From: Matt Doucleff <matt(at)digitalfountain(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need "InBetween" (not just Before and After) Trigger
Date: 2001-01-13 21:23:27
Message-ID: 3A60C74F.FCD1E226@digitalfountain.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Knowledgeable Postgres hackers,

I want a trigger to happen after constraint checking but before row insertion.

insert_row
{
db_check_constraints();
if (constraints satisfied) {
attempt_action()
if (action_successful) {
return allow_insertion
}
}
return disallow_insertion
}

Doing the constraint checking in C is error prone and difficult to maintain.
That is Postgres' job anyway.

If I use a "before" trigger, the constraints are checked after I've already
performed my action. Thus I have performed my action yet no row is inserted
(which is inconsistent).

If I use an "after" trigger, the constraints are checked first. If the row is
inserted, I attempt to perform my action. If my action fails, then I have an
inserted row but no action, which is also inconsistent.

The only solution I've developed involves a duplicate table and a transaction.

BEGIN
insert into duplicate table with identical constraints
insert into real table with trigger
COMMIT

By inserting to the duplicate table without a trigger, I get constraint
checking performed by the db. Then I insert into the real table with the
"before" trigger. If my action fails, everything fails. Obviously I prefer a
solution not involving duplicate tables or such hacks.

Thank you,

Matt

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2001-01-13 22:16:47 Re: Need "InBetween" (not just Before and After) Trigger
Previous Message Tom Lane 2001-01-13 21:07:07 Re: FreeBSDers paying attention? (was Re: exit status 26)