Thread: ON DELETE CASCADE and TRIGGER
I have three tables: CREATE TABLE table1 ( id char(8) NOT NULL, .... PRIMARY KEY (id) ); CREATE TABLE table2 ( id char(8) NOT NULL, .... PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES table1 (id) ON DELETE CASCADE ); CREATE TABLE table3 ( id char(8) NOT NULL, code char(2) NOT NULL, orders integer NOT NULL, .... PRIMARY KEY (id,code,orders), FOREIGN KEY (id) REFERENCES table2 (id) ON DELETE CASCADE ); Now you can see I must insert a row in table1 1st, then insert a row in table2, then insert the rowS in table3. That is OK Now I want to delete an id in all three tables. How can I delete a row in table1 trigger to delete rows in table2, table3? Since I can't put "ON DELETE CASCADE" for PRIMARY KEY in table1. Now I can only delete a row in table2 which trigger to delete rowS in table3. If I want to CREATE TRIGGER BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE table1_trigger(arg); What I suppose pass to the arg in table1_trigger()? Thank you very much in advance! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --Raymond
Attachment
On Wed, 31 Jan 2001, Raymond Chui wrote: > I have three tables: > > CREATE TABLE table1 ( > id char(8) NOT NULL, > .... > PRIMARY KEY (id) > ); > > CREATE TABLE table2 ( > id char(8) NOT NULL, > .... > PRIMARY KEY (id), > FOREIGN KEY (id) REFERENCES table1 (id) ON DELETE CASCADE > ); > > CREATE TABLE table3 ( > id char(8) NOT NULL, > code char(2) NOT NULL, > orders integer NOT NULL, > .... > PRIMARY KEY (id,code,orders), > FOREIGN KEY (id) REFERENCES table2 (id) ON DELETE CASCADE > ); > > Now you can see I must insert a row in table1 1st, then insert a row in > table2, > then insert the rowS in table3. That is OK > > Now I want to delete an id in all three tables. How can I delete a row > in table1 > trigger to delete rows in table2, table3? Since I can't put "ON DELETE > CASCADE" > for PRIMARY KEY in table1. > Now I can only delete a row in table2 which trigger to delete rowS in > table3. I don't understand the question. If you delete from table1 that should cascade to table2 which does the delete which cascades to table3 (or at least that's what my 7.1 box seems to do). > CREATE TRIGGER BEFORE DELETE ON table1 FOR EACH ROW > EXECUTE PROCEDURE table1_trigger(arg); > > What I suppose pass to the arg in table1_trigger()? You probably wouldn't need to pass an arg if you're doing a specific trigger. You'll get the values of the old row in your trigger already (see docs)