Re: ON DELETE CASCADE and TRIGGER

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)