pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Delete Trigger Issue
3+ messages / 3 participants
[nested] [flat]

* Delete Trigger Issue
@ 2001-08-07 05:15 Sundararajan <sdn@srasys.co.in>
  2001-08-07 05:28 ` RE: Delete Trigger Issue Robby Slaughter <webmaster@robbyslaughter.com>
  2001-08-07 05:42 ` Re: Delete Trigger Issue Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Sundararajan @ 2001-08-07 05:15 UTC (permalink / raw)
  To: pgsql-sql

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.Even if the trigger is
written is SQL

Thanks
sundar




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Delete Trigger Issue
  2001-08-07 05:15 Delete Trigger Issue Sundararajan <sdn@srasys.co.in>
@ 2001-08-07 05:28 ` Robby Slaughter <webmaster@robbyslaughter.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Robby Slaughter @ 2001-08-07 05:28 UTC (permalink / raw)
  To: Sundararajan <sdn@srasys.co.in>; pgsql-sql

Let me make sure I get this right:

CREATE TABLE table1
 ( field1 varchar(64),
    ... );

CREATE TABLE table2
 ( field2 varchar(64),
   ... );

and you want that whenever a row is deleted from table1
you want the SAME row to be deleted from table2?

here's what you want. First, a trigger:

CREATE TRIGGER update_table2
BEFORE DELETE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE update_table2_proc();

That trigger will make sure that each time a row is deleted
from table1, the proceudre update_table2_proc will
be called. And here is that procedure

CREATE FUNCTION update_table2_proc()
RETURNS opaque
AS
'BEGIN
   DELETE FROM table2 WHERE field2 = new.field1;
   RETURN new;
 END;'
LANGUAGE 'plpgsql';

That procedure just DELETEs all the rows in table2
that match up to field1 in the first table.
Of course, you might want to do a broader
LIKE matching if they are really VARCHAR fields.

Hope that helps!

-Robby Slaughter



-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Sundararajan
Sent: Tuesday, August 07, 2001 12:16 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Delete Trigger Issue


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.Even if the trigger is
written is SQL

Thanks
sundar


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Delete Trigger Issue
  2001-08-07 05:15 Delete Trigger Issue Sundararajan <sdn@srasys.co.in>
@ 2001-08-07 05:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-08-07 05:42 UTC (permalink / raw)
  To: Sundararajan <sdn@srasys.co.in>; +Cc: pgsql-sql

On Tue, 7 Aug 2001, Sundararajan wrote:

> I am developing a db application in postgresql and i need to write a delete
> trigger on one of the tables.
> 
> 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.

Well, if you want the other semantics (not allow rows in 2 that don't
match rows in 1, etc) you could use a foreign key rather than an explicit
trigger.

> 
> 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';

Are you actually making the trigger?  What does it do
when you try to make the trigger and then when you try
to delete?




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-08-07 05:42 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-07 05:15 Delete Trigger Issue Sundararajan <sdn@srasys.co.in>
2001-08-07 05:28 ` Robby Slaughter <webmaster@robbyslaughter.com>
2001-08-07 05:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox