From kyle@actarg.com Tue May 30 00:13:07 2000 Received: from chi.actarg.com ([209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA53305 for ; Mon, 29 May 2000 20:13:07 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id SAA14382; Mon, 29 May 2000 18:13:05 -0600 Message-ID: <39330791.64796672@actarg.com> Date: Mon, 29 May 2000 18:13:05 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: JanWieck@Yahoo.com CC: pgsql-sql@postgresql.org Subject: create constraint trigger Content-Type: multipart/mixed; boundary="------------F1D6E0E960BAC2A773ABD683" X-Archive-Number: 200005/188 This is a multi-part message in MIME format. --------------F1D6E0E960BAC2A773ABD683 Content-Type: multipart/alternative; boundary="------------24BDB630911A83EEFC12CD91" --------------24BDB630911A83EEFC12CD91 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------24BDB630911A83EEFC12CD91 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
 
  --------------24BDB630911A83EEFC12CD91-- --------------F1D6E0E960BAC2A773ABD683 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" 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 --------------F1D6E0E960BAC2A773ABD683-- From kyle@actarg.com Wed May 31 19:56:10 2000 Received: from chi.actarg.com ([209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA87823 for ; Wed, 31 May 2000 15:56:10 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id NAA18175; Wed, 31 May 2000 13:56:07 -0600 Message-ID: <39356E57.BCB3A1D5@actarg.com> Date: Wed, 31 May 2000 13:56:07 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: JanWieck@Yahoo.com CC: pgsql-sql@postgresql.org Subject: Re: create constraint trigger References: <39330791.64796672@actarg.com> Content-Type: multipart/mixed; boundary="------------0DAB359AFEE496CAD56C0E6E" X-Archive-Number: 200005/228 This is a multi-part message in MIME format. --------------0DAB359AFEE496CAD56C0E6E Content-Type: multipart/alternative; boundary="------------C4DFD79B283DC4754A9A2844" --------------C4DFD79B283DC4754A9A2844 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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 --------------C4DFD79B283DC4754A9A2844 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
  --------------C4DFD79B283DC4754A9A2844-- --------------0DAB359AFEE496CAD56C0E6E Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" 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 --------------0DAB359AFEE496CAD56C0E6E--