From: | "Robby Slaughter" <webmaster(at)robbyslaughter(dot)com> |
---|---|
To: | "Sundararajan" <sdn(at)srasys(dot)co(dot)in>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | RE: Delete Trigger Issue |
Date: | 2001-08-07 05:28:28 |
Message-ID: | EPEHLKLEHAHLONFOKNHNAEPADDAA.webmaster@robbyslaughter.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Sundararajan
Sent: Tuesday, August 07, 2001 12:16 AM
To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2001-08-07 05:42:03 | Re: Delete Trigger Issue |
Previous Message | Sundararajan | 2001-08-07 05:15:39 | Delete Trigger Issue |