Re: help on delete trigger.

Sundararajan wrote:
> I am developing a db application in postgresql and i need to write a delete
> trigger on one of the tables.
>
> the environment is
>
> table1
>
> field1 varchar(64)
> other fields.
>
> table 2.
>
> field1 varchar(64)
> other fields
>
> I need a delete trigger on the table 1, so that if I delete a row from table
> 1 , the corresponding rows from table 2 should also be deleted.
>
> This is the code I have tried.
>
> DROP FUNCTION ApplicationsDeleteFn();
> CREATE FUNCTION ApplicationsDeleteFn()
> RETURNS OPAQUE
> AS '
> BEGIN
> delete from ports where appName=OLD.appName;
> RETURN OLD;
>
> END;
> '
> LANGUAGE 'plpgsql';
>
> Please help me with this, as my work is time bound.

I don't see anything wrong with the trigger you have (except
that you use appName instead of field1).

But shouldn't that be a foreign key reference with ON DELETE
CASCADE and ON UPDATE CASCADE in the first place? This would
automatically delete the referencing rows, so no need for a
custom trigger. In addition, it'll ensure that you can't get
entries into ports for non-existent applications at all.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com