pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate constraint trigger
2+ messages / 1 participants
[nested] [flat]
* create constraint trigger
@ 2000-05-30 00:13 Kyle Bateman <kyle@actarg.com>
2000-05-31 19:56 ` Re: create constraint trigger Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 2+ messages in thread
From: Kyle Bateman @ 2000-05-30 00:13 UTC (permalink / raw)
To: JanWieck@Yahoo.com; +Cc: pgsql-sql
Hi Jan:
In response to your suggestion about possibly being able to use "create
constraint trigger," I have tried the following:
I have the following trigger function (probably not all that important
what it does...):
create function prd_parm_tf_iu ()
returns opaque as '
declare
trec record;
baseholder varchar;
begin
select into trec min(pos) as n,max(pos) as m,count(*) as c
from prd_parm where new.base = base;
if trec.n != 1 or trec.m != trec.c then
raise exception \'Parameters for base % not in order\',
new.base;
end if;
return new;
end;' language 'plpgsql';
This seems to work pretty good:
create trigger prd_part_tr_iu after insert or update on prd_parm
for each row execute procedure prd_parm_tf_iu();
But when I create it with "create constraint trigger" as shown next, the
trigger doesn't seem to be invoked. It says it is created, but it
allows data to pass that would not be allowed with the "create
trigger." So I'm assuming I'm doing something wrong.
create constraint trigger prd_part_tr_iu after insert or update on
prd_parm
deferrable initially deferred
for each row execute procedure prd_parm_tf_iu();
Any ideas?
Also, its not clear to me how to drop a trigger created with "create
constrataint trigger."
It would be very helpful if I could figure out how to make my triggers
deferred. There are things stored in one table that have to be a
certain way if other things are a certain way in a related table. It is
more than what foreign key relationships can handle. It would really be
great if I could figure out how to have the backend monitor the
consistency of these details.
Without deferral, I can check the consistency in one direction. But if
I try to constrain it in both directions, I get into a problem where I
can't update either table without breaking the constraints. I'm
guessing that if I can get deferral to work, I should be able to update
both tables in a transaction and be OK.
Kyle
Attachments:
kyle.vcf (text/x-vcard, 290B) download
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.com
org:Action Target Inc
adr:;;PO Box 636;Provo;UT;84603;US
version:2.1
email;internet:kyle@actiontarget.com
title:President
x-mozilla-cpt:;-15520
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: create constraint trigger
2000-05-30 00:13 create constraint trigger Kyle Bateman <kyle@actarg.com>
@ 2000-05-31 19:56 ` Kyle Bateman <kyle@actarg.com>
0 siblings, 0 replies; 2+ messages in thread
From: Kyle Bateman @ 2000-05-31 19:56 UTC (permalink / raw)
To: JanWieck@Yahoo.com; +Cc: pgsql-sql
Kyle Bateman wrote:
> Hi Jan:
>
> But when I create it with "create constraint trigger" as shown next,
> the trigger doesn't seem to be invoked. It says it is created, but it
> allows data to pass that would not be allowed with the "create
> trigger." So I'm assuming I'm doing something wrong.
>
> create constraint trigger prd_part_tr_iu after insert or update on
> prd_parm
> deferrable initially deferred
> for each row execute procedure prd_parm_tf_iu();
OK, here's a reply to my own post. I've got the constraint trigger
working now. The problem was that it was never getting dropped. I
still don't know how to drop the constraint. I thought by dropping the
trigger function it would get the idea. When I ran the create
constraint again, it said it was created (no errors) so I figured
everything was OK, but it was still running the previously defined
constraint (apparently).
I dropped the table and started from scratch and it seems to work fine
now.
So is there a way to "drop constraint trigger" without having to drop
the table?
Kyle
Attachments:
kyle.vcf (text/x-vcard, 290B) download
begin:vcard
n:Bateman;Kyle
tel;fax:801-377-8096
tel;work:801-377-8033x101
x-mozilla-html:FALSE
url:www.actiontarget.com
org:Action Target Inc
adr:;;PO Box 636;Provo;UT;84603;US
version:2.1
email;internet:kyle@actiontarget.com
title:President
x-mozilla-cpt:;-15520
fn:Kyle Bateman
end:vcard
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-05-31 19:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-30 00:13 create constraint trigger Kyle Bateman <kyle@actarg.com>
2000-05-31 19:56 ` Kyle Bateman <kyle@actarg.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox