Обсуждение: Unable to Delete a Row

Kevin,
Is it anything to do with the following from the trigger procedure
section:
.......
 A trigger function must return either null or a record/row value having
exactly the structure of the table the trigger was fired for.

Row-level triggers fired BEFORE may return null to signal the trigger
manager to skip the rest of the operation for this row (i.e., subsequent
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
this row). If a nonnull value is returned then the operation proceeds
with that row value. Returning a row value different from the original
value of NEW alters the row that will be inserted or updated (but has no
direct effect in the DELETE case). To alter the row to be stored, it is
possible to replace single values directly in NEW and return the
modified NEW, or to build a complete new record/row to return.
......
And the fact that you're returning null?

On Wed, 2005-05-25 at 17:16, Kevin Crenshaw wrote:
> Paul,
>
> Thanks for responding.  I tried your suggestion and it didn't work.
> However, I found that when I removed a trigger from the contacts table,
> the deletion completed successfully.  Here is the text for the trigger
> function:
>
> CREATE OR REPLACE FUNCTION tg_remove_contact() RETURNS TRIGGER AS $$
>       BEGIN
>             UPDATE synchronization SET db_updated = current_timestamp,
> action = 'exch_del'
>                   WHERE OLD.contactid = synchronization.contactid;
>       RETURN NULL;
>       END;
> $$ LANGUAGE 'plpgsql';
>
> CREATE TRIGGER tg_del_contact BEFORE DELETE
>       ON contacts
>       FOR EACH ROW
>       EXECUTE PROCEDURE tg_remove_contact();
>
> Do you know why this trigger would prevent me from deleting a row from
> the contacts table?
>
> Thanks!
>
>
> kevin crenshaw
>
>
>   _____
>
> From: paul@entropia.co.uk [mailto:paul@entropia.co.uk]
> Sent: Wednesday, May 25, 2005 10:32 AM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Unable to Delete a Row
>
> Try:
> DELETE FROM CONTACTS WHERE CONTACTID = '853';
>
> >
> > I am running Postgres v8.0.1 on Win XP Pro. When I try to delete a
> record, which I know exists,
> > I simply get ~DELETE 0(tm) as a response. Please Help!
> >
> > Here is a little more info:
> >
> > DELETE FROM CONTACTS WHERE CONTACTID = 853;
> >
> > Query returned successfully: 0 rows affected, 62 ms execution time.
> > -- Executing query:
> > Select * from contacts where contactid = 853;
> >
> >
> > Total query runtime: 15 ms.
> > Data retrieval runtime: 16 ms.
> > 1 rows retrieved.
> >
> > -- Executing query:
> > EXPLAIN ANALYSE Delete from contacts where contactid = 853;
> >
> >
> > Total query runtime: 63 ms.
> > Data retrieval runtime: 0 ms.
> > 3 rows retrieved.
> >
> > Thanks in advance
> >
> > kevin crenshaw
> >
> >
>
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772

----- Original Message -----

From: Kevin Crenshaw

To: paul@entropia.co.uk ; pgsql-novice@postgresql.org

Sent: Wednesday, May 25, 2005 10:16 AM

Subject: Re: [NOVICE] Unable to Delete a Row

Paul,

 

Thanks for responding.  I tried your suggestion and it didn’t work.  However, I found that when I removed a trigger from the contacts table, the deletion completed successfully.  Here is the text for the trigger function:

 

CREATE OR REPLACE FUNCTION tg_remove_contact() RETURNS TRIGGER AS $$

      BEGIN

            UPDATE synchronization SET db_updated = current_timestamp, action = 'exch_del'

                  WHERE OLD.contactid = synchronization.contactid;

      RETURN NULL;

      END;

$$ LANGUAGE 'plpgsql';

 

CREATE TRIGGER tg_del_contact BEFORE DELETE

      ON contacts

      FOR EACH ROW

      EXECUTE PROCEDURE tg_remove_contact();

 

Do you know why this trigger would prevent me from deleting a row from the contacts table?

 

Thanks!

 

 

kevin crenshaw

 

 

---

From: paul@entropia.co.uk [mailto:paul@entropia.co.uk]
Sent: Wednesday, May 25, 2005 10:32 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Unable to Delete a Row

 

Try:

DELETE FROM CONTACTS WHERE CONTACTID = '853';

 

>

> I am running Postgres v8.0.1 on Win XP Pro. When I try to delete a record, which I know exists,

> I simply get ˜DELETE 0™ as a response. Please Help!

>

> Here is a little more info:

>

> DELETE FROM CONTACTS WHERE CONTACTID = 853;

>

> Query returned successfully: 0 rows affected, 62 ms execution time.

> -- Executing query:

> Select * from contacts where contactid = 853;

>

>

> Total query runtime: 15 ms.

> Data retrieval runtime: 16 ms.

> 1 rows retrieved.

>

> -- Executing query:

> EXPLAIN ANALYSE Delete from contacts where contactid = 853;

>

>

> Total query runtime: 63 ms.

> Data retrieval runtime: 0 ms.

> 3 rows retrieved.

>

> Thanks in advance

>

> kevin crenshaw

>

>

 

"Derrick Betts" <derrick@grifflink.com> writes:
> I think that you need to "RETURN NEW;" in your tg_remove_contact()
> function rather than "RETURN NULL;"

Actually, since it's a DELETE trigger, there is no NEW row;
he needs to RETURN OLD, instead, to give permission for the delete
to continue.

Given what the trigger is doing (ie, propagating the update to another
table), he might be better off to use an AFTER DELETE trigger instead
of BEFORE DELETE anyway --- in which case the return value doesn't
matter and so RETURN NULL is fine.  The point of AFTER DELETE is that
you can be certain the delete really did happen, and wasn't cancelled
by some other BEFORE DELETE trigger.

            regards, tom lane

Thanks for all of your help on this one!


kevin crenshaw


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, May 25, 2005 5:26 PM
To: Derrick Betts
Cc: Kevin Crenshaw; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Unable to Delete a Row

"Derrick Betts" <derrick@grifflink.com> writes:
> I think that you need to "RETURN NEW;" in your tg_remove_contact()
> function rather than "RETURN NULL;"

Actually, since it's a DELETE trigger, there is no NEW row;
he needs to RETURN OLD, instead, to give permission for the delete
to continue.

Given what the trigger is doing (ie, propagating the update to another
table), he might be better off to use an AFTER DELETE trigger instead
of BEFORE DELETE anyway --- in which case the return value doesn't
matter and so RETURN NULL is fine.  The point of AFTER DELETE is that
you can be certain the delete really did happen, and wasn't cancelled
by some other BEFORE DELETE trigger.

            regards, tom lane