Re: undeleteable records - Mailing list pgsql-general

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@pantz.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.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

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