Re: Unable to Delete a Row - Mailing list pgsql-novice

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