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
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) |