Re: undeleteable records

Hey Justin - Tara and I figured out that a trigger on this table - which is
supposed to archive deleted information - blocks deletes altogether. We
lifted this trigger from open ACS... we are having some difficulties with
all such triggers. When we re-create the table w/o the trigger all is
well. Hope to get some time to figure out what is wrong with these damn
triggers! ;)

BTW, I read your post to Dr. Evil re: rules. I am just beginning to play
around with rules - found your message to be helpful. Thanks!

-Ryan Mahoney

At 12:28 PM 7/18/01 +1000, Justin Clift wrote:

>Hi Tara,
>
>Is it possible you have a RULE defined (CREATE RULE ...) which is
>rewriting the query and preventing the record from being deleted?
>
>At those times when creating entries in a table which need to be
>permanently there (not to ever be deleted), I use RULEs to re-write the
>query so it doesn't delete those entries I want protected. What you are
>experiencing is the result I get when using such rules.
>
>i.e. SQL query that should work, but isn't. :)
>
>What do you think?
>
>Regards and best wishes,
>
>Justin Clift
>
>Tara Cooper wrote:
> >
> > Has anyone encountered a problem where a delete sql statement works
> > (no parser errors) but always returns a "DELETE 0" value, even when
> > I'm sure the record I'm selecting for delete is in the table? I have
> > also tried deleting the same record by specifying a different field
> > value, with the same results, as well as trying all of this with
> > multiple records in the same table. None of the records will delete.
> > I've copied the table, the select statement showing the record I want
> > to delete in that table, and the delete statement itself. I'm
> > currently using postgres-sql 7.1 rc2. (I know, I know...but it's a
> > development server.) Thanks!
> >
> > -T
> >
> > DELETE FROM ec_gift_certificates WHERE gift_certificate_id = 9;
> > DELETE 0
> >
> > SELECT gift_certificate_id FROM ec_gift_certificates WHERE
> > gift_certificate_id = 9;
> >
> > gift_certificate_id
> > ---------------------
> > 9
> > (1 row)
> >
> > \d ec_gift_certificates
> > Table "ec_gift_certificates"
> > Attribute | Type | Modifier
> > ------------------------+--------------------------+-------------
> > gift_certificate_id | integer | not null
> > gift_certificate_state | character varying(50) | not null
> > amount | numeric(30,6) | not null
> > amount_remaining_p | character(1) | default 't'
> > issue_date | timestamp with time zone |
> > authorized_date | timestamp with time zone |
> > claimed_date | timestamp with time zone |
> > issued_by | integer |
> > purchased_by | integer |
> > expires | timestamp with time zone |
> > user_id | integer |
> > claim_check | character varying(50) |
> > certificate_message | character varying(200) |
> > certificate_to | character varying(100) |
> > certificate_from | character varying(100) |
> > recipient_email | character varying(100) |
> > voided_date | timestamp with time zone |
> > voided_by | integer |
> > reason_for_void | character varying(4000) |
> > last_modified | timestamp with time zone | not null
> > last_modifying_user | integer | not null
> > modified_ip_address | character varying(20) | not null
> > Indices: ec_gc_by_amount_remaining,
> > ec_gc_by_claim_check,
> > ec_gc_by_state,
> > ec_gc_by_user,
> > ec_gift_certificates_pkey
> > Constraints: ((amount_remaining_p = 'f'::bpchar) OR
> > (amount_remaining_p = 't'::bpchar))
> > ((user_id NOTNULL) OR (claim_check NOTNULL))
> >
> > ***********************************************
> > Tara Cooper
> > Payment Alliance, Inc.
> > carat(at)pantz(dot)com
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com)
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01