Received: from mail.utexas.edu (wb2-a.mail.utexas.edu [128.83.126.136]) by postgresql.org (8.11.3/8.11.4) with SMTP id f775QN671745 for ; Tue, 7 Aug 2001 01:26:23 -0400 (EDT) (envelope-from webmaster@robbyslaughter.com) Received: (qmail 11060 invoked by uid 0); 7 Aug 2001 05:26:22 -0000 Received: from adsl-65-67-62-24.dsl.austtx.swbell.net (HELO robmanbox) (65.67.62.24) by umbs-smtp-2 with SMTP; 7 Aug 2001 05:26:22 -0000 Reply-To: From: "Robby Slaughter" To: "Sundararajan" , Subject: RE: Delete Trigger Issue Date: Tue, 7 Aug 2001 00:28:28 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 In-Reply-To: <027101c11eff$ffb48940$3d0210ac@arula> Importance: Normal X-Archive-Number: 200108/88 X-Sequence-Number: 4309 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